Excluding hidden cell values from a COUNTIF formula

[nate209]

This formula =COUNTIF(C15:C379,"l") returns a result for how many times an employee has been late YTD. Each row is one day of the year and if I filter by date, lets say for the first quarter, the formula will still return a result for the entire range. What would I need to do so that the returned value will represent the sum of only visible cells in the source column after filtering? any help would be appreciated.

 

[mrhstn]

Hi

Perhaps use a helper column (which can be hidden) but lets say column D for the example

In that helper column you could put =--(C15="i") then then copy it down to D379.

Rather than countif use SUBTOTAL to sum up the helper column =SUBTOTAL(109, D15:D379)

It will exclude hidden rows from the count.

HTH