Help, I need a formula for work.

[Che028]

Help, I need a formula for work.
Here is a generic version of my problem?


Leauge Team Attendance exceed 20,000?

American Yankees Yes
American Red Sox No
National Phillies Yes
National Mets Yes
National Giants Yes
National Dodgers No
American Twins No
American Angels Yes

=COUNTIF(E3:E10,"YES")
Total would be 5 "YES'' Answers
______________________________________________
Now the spreadsheet is FILTERED to only show "AMERICAN"

Leauge Team Attendance exceed 20,000?

American Yankees Yes
American Red Sox No
American Twins No
American Angels Yes

=COUNTIF(E3:E10,"YES")

Does anybody know of a formula that I may use the same range (e3:e10
but now since it is FILTERED the answer would be 2 instead of 5?

Any help would be greatly appreciated. Thanks in advance

 

[Ken Johnson]

Help, I need a formula for work.
Here is a generic version of my problem?

        Leauge  Team    Attendance exceed 20,000?      

        American             Yankees         Yes        
        American             Red Sox         No
        National              Phillies       Yes        
        National              Mets                       Yes    
        National              Giants         Yes        
        National              Dodgers        No
        American              Twins         No
        American              Angels         Yes        

=COUNTIF(E3:E10,"YES")
Total would be 5 "YES'' Answers
        ______________________________________________                  
Now the spreadsheet is FILTERED to only show "AMERICAN"

        Leauge  Team    Attendance exceed 20,000?      

        American        Yankees Yes    
        American        Red Sox No      
        American        Twins   No      
        American        Angels  Yes    

=COUNTIF(E3:E10,"YES")

Does anybody know of a formula that I may use the same range (e3:e10)
but now since it is FILTERED the answer would be 2 instead of 5?

Any help would be greatly appreciated. Thanks in advance.

You could use the SUBTOTAL function but you will need to have a column
with 1 when the corresponding column E value is "Yes" and 0 when it
isn't. This column of 1s and 0s could be achieved using =IF(E3="Yes",
1,0) filled down as far as required.
Say the column with the 1s and 0s is column F, then the formula that
would count the visible "Yes" values would be...

=SUBTOTAL(109,F3:F10)

Ken Johnson

 

[Joe Mac]

Why not place the data into a Pivot Table and let Excel do the manipulation
without any formulas required... Spin the data to what ever view you
wish/need...
Place the cursor on any cell in a contiguous range and select
Data>PivotTable and PivotChart Reporting...