Count Blanks in a Filtered Column

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

"JMB" wrote in message
...
Maybe add a new column to the table and enter

=--(Cell("type", CellRef)="b")

and copy it down. Then use Subtotal(9, RangeRef) to get a count. Cell
seems to distinguish between truly empty cells and those that appear
empty.
Interestingly, it seems Microsoft is also confused on the terminology.
According to help, the CELL function:

"Returns "b" for blank if the cell is empty"

Couldn't get it to work in an array formula, so it's still a two step
process.

"Biff" wrote:

you didn't read all of the posts : )

I did!

Never even thought of that.

I didn't think of it, you did!

I thought about selecting all the EMPTY cells and padding them with a
formula blank.

I just thought that there might be a formula that didn't "depend" on
another
column of data being present.

Biff

"JMB" wrote in message
...
you didn't read all of the posts : )

"Biff" wrote:

=SUBTOTAL(3,OtherColumn)-SUBTOTAL(3,GivenColumn)

Doh!

Never even thought of that.

As for cells that contain things that evaluate to ""

Aladins formula seems to work for that. Lightly tested.

Biff

"Harlan Grove" wrote in message
oups.com...
Biff wrote...
...
How would you count EMPTY cells? I haven't been able to figure it
out.
...

Presumably you mean cells containing nothing. If there were another
column in the filtered table that always contained something, then

=SUBTOTAL(3,OtherColumn)-SUBTOTAL(3,GivenColumn)

would count the number of cells in GivenColumn that contain nothing.
As
for cells that contain things that evaluate to "", counting them
requires udfs.

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 ="".

Biff wrote:
Clarification:


That doesn't work.........


If the cells are EMPTY, it does work if the cells have formula BLANKS.

Don't know whether the OP meant "blank" or empty.

How would you count EMPTY cells? I haven't been able to figure it out.

Biff

"Biff" wrote in message
...

That doesn't work. Result = 0, unfiltered or filtered. I tried that
formula (along with many other variations) earlier.

Typo in the formula, one too many ")"

ROW(B5)),,1))

ROW(B5),,1))

Biff

"Aladin Akyurek" wrote in message
. ..

Assuming that the secret column range is B5:B200...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B2 00)-ROW(B5)),,1)),--(B5:B200=""))

Gos-C wrote:

Hi,

Is there a function to count blanks in a filtered column? That is, if
I filter a column to show the rows that are blank, can I use the
subtotal function (or some other) to count the number of blanks?

Thanks,
Gos-C