# Count Visible Cells

#### DougStroud

##### Well-known Member
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.

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

##### MrExcel MVP
subtotal() ignores filtered cells, so

=subtotal(3,a2:a66)

#### DougStroud

##### Well-known Member
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

#### DougStroud

##### Well-known Member
subtotal() ignores filtered cells, so

=subtotal(3,a2:a66)

Curiously enough, when I used a "3" as your original formula is written, it appears to work. What does the "3" do?

##### MrExcel MVP

'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
'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

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:

##### MrExcel MVP
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
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!

##### MrExcel MVP
Well - you had subtotal() and sumproduct() in there

Replies
6
Views
49
Replies
5
Views
95
Replies
3
Views
23
Replies
12
Views
127
Replies
2
Views
60