数组公式当前行中的COUNTA(Google表格) [英] COUNTA in current row in an array formula (Google Sheets)

问题描述

我有一个固定数量的列和动态行的Google表格.

I have a Google sheet with fixed number of columns and dynamic rows.

我喜欢使用countA对当前行中具有非空白值的字段进行计数.

I like to use countA to count fields with a value (non-blank) in the current row.

我在这里找到了公式，但不理解，两者都无法正常工作.

I found a formula here but don't understand it, neither can get it to work.

ArrayFormula(MMULT( LEN(A1:E)>0 ; TRANSPOSE(SIGN(COLUMN(A1:E1)))))

Sheet给我一个错误:函数MMULT参数1需要数字值.但是'TRUE'是布尔值，不能强制为数字."

Sheet gives me error: "Function MMULT parameter 1 expects number values. But 'TRUE' is a boolean and cannot be coerced to a number."

推荐答案

如果将LEN(A1:E)> 0返回的布尔值(真或假)转换为数字(1或0)，则该公式应该起作用巴里已经提到过.通过将LEN()函数的输出包装在N函数或在其前面加上-".因此，假设您的数据从第2行开始，请查看是否可行:

The formula should work if you convert the booleans (true or false) returned by LEN(A1:E)>0 to numbers (1 or 0), as Barry already mentioned. This can be done quite easily by wrapping the output of the LEN()-function in an N-function or by preceding it with '--'. So, assuming your data starts in row 2, see if this works:

=ArrayFormula(MMULT( --(LEN(A2:E)>0) , TRANSPOSE(COLUMN(A2:E2)^0)))

另一种方法是使用COUNTIF()

An alternative way would be to use COUNTIF()

=ArrayFormula(COUNTIF(IF(A2:E<>"", row(A2:A),),row(A2:A)))

甚至可能也可以使用:

=ArrayFormula(MMULT( --(A2:E<>"") , TRANSPOSE(COLUMN(A2:E1)^0)))

如果您还想包含标题行，请尝试:

If you also want to include a header row, try:

=ArrayFormula(if(row(A:A)=1, "Header", MMULT( --(LEN(A:E)>0) , TRANSPOSE(COLUMN(A1:E1)^0))))

或

 =ArrayFormula(if(row(A:A)=1, "Header", MMULT( --(A:E<>"") , TRANSPOSE(COLUMN(A1:E1)^0))))

或

=ArrayFormula(if(row(A:A)=1, "Header", COUNTIF(IF(not(isblank(A:E)), row(A:A),),row(A:A))))

(在评论中的新问题之后)

(after new question in comments)

如果您想对值求和，也可以使用MMULT()来完成:

If you want to sum the values, you can do that with MMULT() too:

=ArrayFormula(if(row(A:A)=1, "Header", MMULT(if(A1:E<>"", A1:E,0), transpose(column(A1:E1)^0))))

或使用sumif:

=ArrayFormula(if(row(A:A)=1, "Header", sumif(IF(COLUMN(A1:E1),ROW(A1:A)),ROW(A1:A),A1:E)))

注意:如果您想将输出限制为例如在col A中具有值的最后一行，请尝试:

NOTE: if you want to limit the output to let's say the last row that has values in col A, try:

=ArrayFormula(if(row(A:A)=1, "Header", IF(LEN(A1:A), MMULT(if(A1:E<>"", A1:E,0), transpose(column(A1:E1)^0)),)))

或再次使用sumif()

or, again with sumif()

=ArrayFormula(if(row(A:A)=1, "Header", if(len(A1:A), sumif(IF(COLUMN(A1:E1),ROW(A1:A)),ROW(A1:A),A1:E),)))

这篇关于数组公式当前行中的COUNTA(Google表格)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！