Array of Counts

[BrianGGG]

Hello. I'd like to figure out a formula in a single cell that returns an array of counts rather than an overall grand total count for a given list of columns.

Example. If I have the following 3 columns and four rows:

 x          x
       x
       x
       x    x

The answer I am looking for is {1,3,2} because the COUNTA value of each column would be 1,3,2 respectively.
If I try something like COUNTA(A1:A4:C1:C4) I just get the grand total of 6 as a single value.


Any ideas how to return the COUNTA value for columns in an array?




thanks
BrianGGG

 

[Eric W]

Try:

=MMULT(TRANSPOSE(ROW(A1:A4))^0,--(A1:C4<>""))

with Control+Shift+Enter. You can use this as part of another formula, or select a 3-column range when entering the formula.

 

[BrianGGG]

This is exactly what I needed. Thanks.

 

[Eric W]

You're welcome.