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.

Hi,

Try this

SUBTOTAL(103,range)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Che028" wrote in message
...

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.




--
Che028

Not if it is filtered to show American, it would return 4 and not 2


=SUMPRODUCT(--(E2:E10="YES"),--SUBTOTAL(3,OFFSET($E$2,ROW($E$2:$E$10)-MIN(ROW($E$2:$E$10)),,)))


will return 2


--


Regards,


Peo Sjoblom



"Ashish Mathur" wrote in message
...
Hi,

Try this

SUBTOTAL(103,range)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Che028" wrote in message
...

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.




--
Che028

THANK YOU Peo Sjoblom! THIS FORMULA WILL SAVE ME SO MUCH TIME!

My pleasure

--


Regards,


Peo Sjoblom

"Che028" wrote in message
...

THANK YOU Peo Sjoblom! THIS FORMULA WILL SAVE ME SO MUCH TIME!




--
Che028

What about:
SUBTOTAL(3,range)

Not sure why this simple version wouldn't work, if Autofilter is being
used.

Because it is filtered for American only, of course you
can apply another filter for yes/no but given the OP
requirements it won't work unless you do that.
Of course it can be that the yes/no column is already filtered by something
else?

That formula will de-facto give you the opportunity to do a "countif"
of filtered values


--


Regards,


Peo Sjoblom

"Spiky" wrote in message
...
What about:
SUBTOTAL(3,range)

Not sure why this simple version wouldn't work, if Autofilter is being
used.

Seems like if you are going to the trouble of filtering, you may as
well do it on 2 columns. Instead of filtering one and using a formula
to "filter" the other.

Otherwise, I'd say the best route is to use the formula for both and
skip the filter altogether.

Plenty of options.