counta returning array

[dfsobral]

hy

i want to count cells not empty in each column to use in functions

presently i have row 1 with this information, each column have a counta function (data starts in row3)

is there a function or and array formula that would return this array to use in other functions? (allowing to eliminate this row1 from the spreadsheet)

i could easily do it in VBA but not really an option in this case

 

[Eric W]

Maybe:

=TRANSPOSE(MMULT(TRANSPOSE(--(A2:D10<>"")),ROW(A2:A10)^0))

with Control+Shift+Enter.

 

[dfsobral]

nice, but not usefull to me

if i want to insert that array in a function like sumproduct i would need to use Ctrl+Shift+Enter to the array inside SumProduct but not SumProduct itself

so unless there's some functionality i do not know it will not work

 

[dfsobral]

nevermind
my mistake

thanks, works perfectly

 

[Eric W]

Glad you got it figured out. Just for information, here's a slightly shorter way to code it:

=MMULT(TRANSPOSE(ROW(A2:A10)^0),--(A2:D10<>""))

with CSE.