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