# Thread: Count if with filters Thanks: 0 Likes: 0

1. ## Count if with filters

I have a worksheet with 19 columns and, at the top, I have a summary table with "Countif" statements. I have added filters and I want the "countif" results to change depending on how the user changes the filter on any one of the 19 columns. I know "subtotal" will change depending on visible cells but I cannot get the "Countif" results to change. Is it possible?

Thanks for any comments. I tried to search the message boards to see if this has come up before but I didn't see a related message.

Lynn

2. ## Re: Count if with filters

Care to post one of the CountIf formulas you have?

3. ## Re: Count if with filters

you can do a subtotal on the count of filtered records but countif will not work (as a subtotal) that is.

see here for seperate subtotal numbers

What is Excel SUBTOTAL formula and 5 reasons why you should use it | Chandoo.org - Learn Microsoft Excel Online

4. ## Re: Count if with filters

Care to post one of the CountIf formulas you have?
Of course. I have a table that looks like this:

Bucket Weeks # of Assoc % of terms
1 0-13 =COUNTIF(\$R\$11:\$R\$85270,H2 J2/\$J\$7
2 14-26 =COUNTIF(\$R\$11:\$R\$85270,H3 J3/\$J\$7
3 27-39 =COUNTIF(\$R\$11:\$R\$85270,H4 J4/\$J\$7
4 40-52 =COUNTIF(\$R\$11:\$R\$85270,H5 J5/\$J\$7
5 > 52 =COUNTIF(\$R\$11:\$R\$85270,H6 J6/\$J\$7
Total XXXXXX

5. ## Re: Count if with filters

Opps, it came out all run together. The count if statement is simply =countif(\$R\$11:\$R\$85270,H2)

6. ## Re: Count if with filters

Thanks, yes I figured the countif formulat would not work like the subtotal with the filters. Is there a way around it or another function I can use?

7. ## Re: Count if with filters

Originally Posted by lcohenvb
Of course. I have a table that looks like this:

Bucket Weeks # of Assoc % of terms
1 0-13 =COUNTIF(\$R\$11:\$R\$85270,H2 J2/\$J\$7
2 14-26 =COUNTIF(\$R\$11:\$R\$85270,H3 J3/\$J\$7
3 27-39 =COUNTIF(\$R\$11:\$R\$85270,H4 J4/\$J\$7
4 40-52 =COUNTIF(\$R\$11:\$R\$85270,H5 J5/\$J\$7
5 > 52 =COUNTIF(\$R\$11:\$R\$85270,H6 J6/\$J\$7
Total XXXXXX
Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(\$R\$11,ROW(\$R\$11:\$R\$85270)-ROW(\$R\$11),0)),--(\$R\$11:\$R\$85270=H2))

8. ## Re: Count if with filters

Wow, thanks, it's doing something but I am trying to figure out exactly what it is doing! I was thinking if I had no filters, it should be the same as my count if but it isn't....

9. ## Re: Count if with filters

Unbelievable, it works! I can't believe how someone on this board can always help me so quickly. Thank you very much!

10. ## Re: Count if with filters

Originally Posted by lcohenvb
Unbelievable, it works! I can't believe how someone on this board can always help me so quickly. Thank you very much!
You are welcome. Thanks for providing feedback.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•