Count Visible Cells

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,956
I need to count the number of visible cells with data after filtering.

Example- Range is: A2-A66, Filter out array of 15 cells. How many cells/rows are left?

I tried the "COUNT" formula, but this is not correct.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,956
subtotal() ignores filtered cells, so

=subtotal(3,a2:a66)

No luck. In column "A" I have differing values. When I tried changing all the values to "1" and try the formula suggested, I just got the value "1", not say 45 for the total visible cells in the range a2:a66
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

'3' is the argument that tells the subtotal() which function to use over the range, in this case counta(). (For example, 1 is average, 4 is max etc - check out the help file entry for the details)
 
Last edited:

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,956
'3' is the argument that tells the subtotal() which function to use over the range, in this case counta(). (For example, 1 is average, 4 is max etc - check out the help file entry for the details)

Thank you!
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,956

ADVERTISEMENT

subtotal() ignores filtered cells, so

=subtotal(3,a2:a66)

What about combining the two formulas: SUBTOTAL(3,F2:f66) and =SUMPRODUCT(--($F$2:$F$66="b")) to return only the visible cells w/ the value "b"?

I tried this: subtotal(3(SUMPRODUCT(--($F$2:$F$66="b"))), =SUBTOTAL(3,F2:F66(SUMPRODUCT(--($F$2:$F$66="b"))))

(They both failed)
 
Last edited:

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
You're on the right track.

The below will count the number of cells in the range f2:f66 that (a) are visible and (b) contain the value 'b':

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2:F66,ROW(F2:F66)-ROW(F2),0,1,1)),--(F2:F66="b"))
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,956
You're on the right track.

The below will count the number of cells in the range f2:f66 that (a) are visible and (b) contain the value 'b':

=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2:F66,ROW(F2:F66)-ROW(F2),0,1,1)),--(F2:F66="b"))

Thank you so much... I don't know about being on the right track... at all-- but thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,626
Messages
5,523,982
Members
409,550
Latest member
baaabies

This Week's Hot Topics

Top