1

In a sheet, I make calculations horizontally, however I can't build the arrayformula to expand calculation vertically on each row. See the following sheet:

https://docs.google.com/spreadsheets/d/1FbeoKhiJ3jqYXqfIgAK_tHXPZ_Z1Xrm33ulu9gomXuE/edit#gid=16063565

enter image description here

Select the tab "arrayformula", and point to the formula in column F

=COUNTA(B3:E3)

How can I apply arrayformula so it can expand to all the rows?

2 Answers 2

1

=ARRAYFORMULA(IF(LEN(A3:A), 
 MMULT(IFERROR(LEN(B3:E)/LEN(B3:E), 0), TRANSPOSE(COLUMN(B2:E2)^0)), ))

0

4

Digging the web I have found a Google Post that states:

Aggregating functions such as sum() will not produce multi-cell results in an arrayformula() wrapper. To calculate row-by-row totals of the numeric values in columns A to C from row 2 down, use simple addition instead of sum():

=arrayformula( A2:A + B2:B + C2:C )

When there are lots of columns, or if the array to be totaled row-by-row is not a range but another array expression, use mmult():

=arrayformula( 
                mmult( A2:C; transpose(sign(column(A2:C))) 
                      ))

See the illustrative sheet prepared by Top Contributor APL+ for more information on mmult() usage.

Some other aggregating functions that are not conducive for use in an array formula are listed below with examples of workarounds for producing row-by-row results.

and() -- use boolean arithmetic:

=arrayformula( if( (A2:A = "OK") * (B2:B > 10); "Ready"; "" ) )

or() -- use boolean arithmetic:

=arrayformula( if( (A2:A = "Yes") + (A2:A = "Maybe"); "Positive"; "" ) )

average() -- use addition and division:

   =arrayformula( (A2:A + B2:B + C2:C) / 3 )

count() -- use isnumber() and +:

  =arrayformula( isnumber(A2:A) + isnumber(B2:B) + isnumber(C2:C) )

counta() -- use not(isblank()) and +:

  =arrayformula( not(isblank(A2:A)) + not(isblank(B2:B)) + not(isblank(C2:C)) )

concatenate() -- use the & operator:

  =arrayformula( A2:A & B2:B & C2:C )

join() -- use the & operator: =arrayformula( A2:A & ", " & B2:B & ", " & C2:C )

max() -- use if():

    =arrayformula( if(A2:A > B2:B; A2:A; B2:B) )

min() -- use if():

    =arrayformula( if(A2:A < B2:B; A2:A; B2:B) )

product() -- use the * operator:

   =arrayformula( A2:A * B2:B * C2:C )
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.