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

 

[Steve=True]

Could you use this?

=COUNTA(Range)+COUNTBLANK(Range)


Steve=True
www.exceldashboardtemplates.com

 

[crusader]

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

Is there a column that will always have data (no blanks)? I realize you may be filtering from a column that may not have data in some rows.

A clearer example will be helpful.

 

[Jonmo1]

If this correctly gives you the number of filtered rows with Y,

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

Then unless my brain is falling out of my head again, this should count all the filtered rows.

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

 

[T. Valko]

If this correctly gives you the number of filtered rows with Y,

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

Then unless my brain is falling out of my head again, this should count all the filtered rows.

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

That doesn't include the empty cells:

I've been trying to find a way to show number of filtered rows included rows with empty cells.

I don't think this can be done unless you use an "anchor" column to base the count on.

For example:

Book1
	A	B
1	Region	Complete
2	South	Y
3	East	Y
4	North	_
5	East	_
6	North	Y
7	West	_
8	North	Y
9	South	Y
10	North	Y
11	East	_
12	East	_
13	East	Y
14	South	_
15	East	Y
16	East	Y
17	South	Y
18	South	_
19	East	Y
20	East	_
Sheet1

You could base the count on visible rows when filtered by region.

Yo could add a helper column to be used as the anchor column if need be.

 

[DADAZHU]

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.

 

[Jonmo1]

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

Picking up my brain....

So you could use the same basic formula, without the ="Y" criteria.
But referenceing a column that does not contain blanks.

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

Change all the references to column F to whatever column does not contain any blanks.

 

[Steve=True]

Strange, the counta + countblank works on my spreadsheet test when filtered.

 

[DADAZHU]

Yes, Jon, I did exactly the same as you mentioned, no need for an additional criteria.

To Steve, I could be wrong, but I think only Subtotal can do the trick when the filter is on..

Cheers

 

[T. Valko]

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.

Ok, then using column A Area as the anchor column:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:F120)-ROW(A2),0,1)))