Sum Each Row in Array (Google Sheets)

Question

I have a complex formula the produces an Array (10+rows and 10+columns). For simplicity's sake, let's just say it's =unique(a1:z10)

I'm looking for a formula that can counta() each Row of the array individually. It should basically return a 1-column array that counts the number of values in each row. Because I will then wrap that in a max() function to see the highest count among them all.

Thanks guys. I hope my question is intelligible. Let me know if further clarification needed.

Answer 1

try:

=MAX(ARRAYFORMULA(MMULT(IFERROR(LEN(B:K)/LEN(B:K), 0), TRANSPOSE(COLUMN(B:K)^0))))

if you want to do it all in one step use:

=MAX(ARRAYFORMULA(MMULT(IFERROR(LEN(B:K)/LEN(B:K), 0), 
 ROW(INDIRECT("A1:A"&TRANSPOSE(COLUMNS(B:K))))^0)))

where you replace B:K ranges with your formula that outputs the array

Answer 2

The standard way of getting row totals of an m rows by n columns array is

=mmult(<array>,<colvector>)

where <array> is an array of numbers and <colvector> is an array n rows high and one column wide containing all ones.

The standard way of getting <colvector> for a range is

=row(<range>)^0

but this doesn't work for an array because you can only use the row function with a range.

So I think you'd have to generate <colvector> another way - the easiest way is to use Sequence, but unfortunately it means repeating the formula for your <array> to get the column count.

Example

Supposing we choose this as our complex array:

=ArrayFormula(if(mod(sequence(10,10),8),"",sequence(10,10)))

a 10 X 10 array with some spaces in it.

The whole formula to get the row counts would be:

=ArrayFormula(mmult(n(if(mod(sequence(10,10),8),"",sequence(10,10))<>""),
sequence(columns(if(mod(sequence(10,10),7),"",sequence(10,10))))^0))