Google Sheets Arrayformula on both horizontal & vertical cells?

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:

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?

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

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 )
``````
• This answer is a lifesaver! Oct 8, 2022 at 12:49