5

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

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 gives me error: "Function MMULT parameter 1 expects number values. But 'TRUE' is a boolean and cannot be coerced to a number."

Ricardo
  • 105
  • 1
  • 7

2 Answers2

11

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)))

An alternative way would be to use COUNTIF()

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

and probably even a combination should work:

=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))))

or

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

or

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

EDIT: (after new question in comments)

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))))

or using sumif:

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

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)),)))

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),)))
JPV
  • 26,499
  • 4
  • 33
  • 48
  • Thanks JPV, I used the first version including the header and it worked just great! – Ricardo May 23 '15 at 10:40
  • The other two also do the job. :) – Ricardo May 23 '15 at 10:50
  • I tried to replace the COUNTIF in the array with SUMIF to add the value of fields but don't get the right result, any hint? – Ricardo May 23 '15 at 11:49
  • Hi Ricardo, I've updated my answer. See if it works for you ? – JPV May 23 '15 at 15:03
  • Ohh mighty excel wizzard - thanks for your help =) Perhaps you have an idea for this questions, too? [SUMIFS](http://stackoverflow.com/questions/30390690/criteria-range-in-sumifs-array-formula?noredirect=1#comment48870226_30390690) & [formating](http://stackoverflow.com/questions/30412550/google-sheets-propagate-column-formatting-to-new-rows?noredirect=1#comment48917074_30412550) – Ricardo May 23 '15 at 17:55
0

That formula seems a little complex for your explanation, can't you just use this formula copied down

=COUNTA(A1:E1)

...but specifically addressing your question, you need to change this part

LEN(A1:E)>0

...so that it returns numbers - try

IF(LEN(A1:E)>0;1;0)

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Thanks for the reply, can be any other formula but I can't copy them just down as dynamic number of rows. A form adds new rows and formulas calculate value for sending autoresponder. – Ricardo May 22 '15 at 10:05
  • The formula works after your adaption! Thanks :) How can I display them also in the right row? – Ricardo May 22 '15 at 10:11
  • Are you actually doing this in Google sheets or excel? – barry houdini May 22 '15 at 10:16
  • Google Sheets - results are all one row to high, covering also the frozen row header. The other columns use: =ARRAYFORMULA(IF(ROW(A:A)=1,"NAME", FORMULA) but this doesn't work. – Ricardo May 22 '15 at 10:18
  • Thanks Barry, the formula worked in combination with the headed provided by JPV. – Ricardo May 23 '15 at 10:41