Use Countif with Filters applied

[jrobin5881]

From Columns A2 to H2 I have column headings to which I applied an autofilter. In cell F3 & G3 I have the formula =SUBTOTAL(9,F4:F991) & =SUBTOTAL(9,G4:G991).So when I change the filtered item in A2 I get the corresponding sums in F3 and G3 each time and that's working fine.

Here's my issue: Column E has a "Y" or an "N" in each row and I need a count of the "Y"s each time the list is filtered on the criteria in A2. I've used the =COUNTIF(E4:E991,"Y")and that gives all "Y"s no matter what selection is made in A2 and I've tried =SUBTOTAL(2,E4:E991)and am not getting the correct results. I've tried concantenating the COUNTIF AND SUBTOTAL (using 9 instead of 2) and that did not work. I tried a straight out IF formula and that did not work. Any suggestions?

 

[Lilliabeth]

Filter col E then use =SUBTOTAL(2,E4:E991)

--Lilliabeth

 

[jrobin5881]

I forgot to add that I tried that as well (filtering for "Y" in col E) and it's not working. I have a 1 appearing there and there is a whole bunch more "Y"s then 1

 

[Lilliabeth]

You use the autofilter drop-down, choose Y and get the Y's and some 1's - is this correct?

What version of Excel?


--Lilliabeth

 

[jrobin5881]

Ver=Excel 2003

No, I set the filter in A2 to a geographic area ie "Eastern" then my list filters to only those records that have "Eastern" in Col. A

F3 and G3 which contain the subtotal for me adds up the data.

I leave Col. A filtered to "Eastern" and set the filter in column E to "Y" and now I have all the records appearing that are in Eastern and have a Y in column E. I'm trying to count the number of records returned each time I change the Area in Column A. The subtotals are working fine but i can't get the count to work properly. It's showing the total number of records and not just the amount that are filtered.

 

[Lilliabeth]

Please be clear.

It's showing the total number of records and not just the amount that are filtered.

What is "It's"?.. are you using this formula or what:


=SUBTOTAL(2,E4:E991)



--Lilliabeth

 

[Lilliabeth]

Change the first argument to a 3 instead of a 2.

=SUBTOTAL(3,E4:E991)


--Lilliabeth

 

[jrobin5881]

That did it! Don't know why I didn't think of that one. Thank you.