subtotal() behavior

[lezawang]

Hi
I want to count number of cells based on color. So I used conditional formatting to highlight cells which has value >70. The under column A I typed =subtotal (2,B2:B25) then I used to auto fill to copy that formula to column C, D, and E.
Now all subtotal() returned 24 (so far so good). Then I used Filter to filter column A based on cell color (Red). All subtotal() functions returned the same number 7. Which is only right for column B not the other. The unfilter and tried to filter column C this time and same story, all subtotals returned 4 which is only correct for column C.
Why subtotal behave like that? Thank you

 

[Scott T]

Formulas do not see cell colors. You can use countif or countifs to count the cells.

=COUNTIF(B2:B10,">70")