Countif on filtered rows

**djcfisher** · 05-21-2012, 03:47 PM

Hi, I am trying to count the number of visible cells containing different values in a filtered column, but I can't find a way to combine the Subtotal and Countif functions. Is this possible? My row range is L4:L87 and the values I want to be able to count the number of occurrences of are "1", "1.25", "2" and "2.50". Many thanks for your help.

**Søren Larsen** · 05-21-2012, 03:54 PM

Is there any specific reaon why you are filtering your data? If it's because you have multiple criteria, then you can use =COUNTIFS().

**Haseeb Avarakkan** · 05-21-2012, 04:01 PM

Try this,

Please Login or Register  to view this content.

Assume L4:L87 formatted or entered as text, ie. to get 2.50

**daddylonglegs** · 05-21-2012, 04:04 PM

Try this formula to count visible 1.25 values

=SUMPRODUCT(SUBTOTAL(2,OFFSET(L4,ROW(L4:L87)-ROW(L4),0))*(L4:L87=1.25))

**djcfisher** · 05-21-2012, 04:25 PM

Hi all, thanks for that, both solutions work brilliantly (Soren, I have to filter the rows because of other criteria in different columns). Much appreciated.