# How to use countif with subtotal

1. ## How to use countif with subtotal

Hi,

I am trying to count number of lines by department over 30 days old.
Countif function works =COUNTIFS(C:C, "First Ops",D:D, ">29")

However when I filter this by owner, eg. filter for "Dave" and "Brian", need the countif value to change also.

Can anyone help me out

Thanks

2. ## Re: How to use countif with subtotal

If you want conditional counting applied only to rows which pass through filters, so are visible, you need to add a column to your table. If your complete data were in A1:P1000 with column labels in row 1, and if col A would never be blank, so COUNTA(A2:A1000) = 999, add formulas.

Q2: =SUBTOTAL(103,A2)

Fill Q2 down into Q3:Q1000. The col Q formulas will return 1 when the row passes through the filter (visible) or 0 when it doesn't (hidden). Then use COUNTIFS formulas like

=COUNTIFS(C\$2:C\$1000,"First Ops",D\$2:D\$1000,">29",Q\$2:Q\$1000,1)

3. ## Re: How to use countif with subtotal

In K7 Cell

Formula:
`Please Login or Register  to view this content.`

Drag it down

4. ## Re: How to use countif with subtotal

Missed to see your second condition which can be covered like this..

Formula:
`Please Login or Register  to view this content.`

5. ## Re: How to use countif with subtotal

You're a hero this works like a dream

There are currently 1 users browsing this thread. (0 members and 1 guests)