Count total Number of filtered rows including empty cells

[DADAZHU]

Hi, everyone,

I've been trying to find a way to show number of filtered rows included rows with empty cells. I had no luck so far.

I have a simple table, with value only "Y" or nothing Under columns C,D,E,F, and G, similar like this,

A B C D E F G
1 Area Type 1 2 3 4 5
2 SYD H Y Y
3 MEL I Y
.

The title of the table(row 1) gets filtered, at bottom of the table, I have no problem to get the number of rows with "Y", eg

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F$2:F$120,ROW(F$2:F$120)-MIN(ROW(F$2:F$120)),0,1)),--(F$2:F$120="Y"))

I am unable to get number of filtered rows with no value, or the total number of filtered rows. I am able to get the info via VBA, but in this instance, no VBA is allowed. One of unsuccessful attempts was as below,
=SUMPRODUCT(SUBTOTAL(3,OFFSET(F$2:F$120,ROW(F$2:F$120)-MIN(ROW(F$2:F$120)),0,1)),--(F$2:F$120="")) , the return was 0. I am not sure it wasn't working.


I think that the solution should be easy, but I've been pulling my hair and making no progress on it.

Your help will be appreciated.

Regards,

David

 

[crusader]

Re: Solved -- Count total Number of filtered rows including empty cells

Thanks all for your help.

To Steve, COUNTA(Range)+COUNTBLANK(Range) will only give me the total rows before the table is filtered. But I actually look for the total number of rows being filtered.

To jonmo1, as T. Valko said, it will only give me the numbers of rows with "Y", doesn't include the those empty cells.

To T. Valko & crusader, both columns(Area & Type) always have data, so I now decide to use one of them as the "anchor" column.

Assuming A is your anchor column with a heading in A1:

=SUBTOTAL(3,A2:A120)

Adjust range as needed.