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 )