Count Non-Blank Rows
Lazzaroni wrote...
Can anyone suggest a more flexible substitute for the following formula? It
effectively counts the number of non-blank (0) rows in an array. This
formula is limited, however, in that every column requires a separate
statement. I would like to find a function that could handle an array of any
size with a single statement.
1 0 0
1 1 0
0 0 0
0 0 1
....
If your range of 1s and 0s were named M, try the following array
formula.
=COUNT(1/(MMULT(M,TRANSPOSE(COLUMN(M)^0))0))
Note: this uses only nonvolatile functions, so it recalculates only
when M changes.
|