Count if with filters

[lcohenvb]

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

 

[Aladin Akyurek]

Care to post one of the CountIf formulas you have?

 

[Dryver14]

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

 

[lcohenvb]

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

 

[lcohenvb]

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

 

[lcohenvb]

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?

 

[Aladin Akyurek]

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))

 

[lcohenvb]

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....

 

[lcohenvb]

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

 

[Aladin Akyurek]

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.