Count Visible Cells

[DougStroud]

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.

 

[PaddyD]

subtotal() ignores filtered cells, so

=subtotal(3,a2:a66)

 

[DougStroud]

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]

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?

 

[PaddyD]

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

 

[DougStroud]

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

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)

 

[PaddyD]

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]

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!

 

[PaddyD]

Well - you had subtotal() and sumproduct() in there