Count Blanks in a Filtered Column

B

Biff

Subtotal(9, RangeRef)

I tried to "leverage" that approach based on the single column attempts.

My logic was an empty cell would have a sum of zero, then just count the
number of zero sums.

It failed!

Subtotal isn't a very "flexible" function, sort of like the Rank function,
you can only do so much with it.

Biff
 
A

Aladin Akyurek

Two points...

1) The suggestion I made has a paren too many at the wrong place.
2) More important: The Subtotal bit should take not the range subject to
counting, rather a range where the filter is applied.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A5:A200,ROW(A5:A200)-ROW(A5),,1)),--(B5:B200=""))

where A5:A200 is object of filtering and B5:B200 object of counting.

This would yield a count of empty cells and formula-blanks.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A5:A200,ROW(A5:A200)-ROW(A5),,1)),--ISBLANK(B5:B200=))

This would yield a count of empty cells only.

ISBLANK ignores cells created with formulas like ="".
 
Top