CountIF with an OR function?

[thenapolitan]

Hey All,

I'm in a little bit of a dilemma trying to calculate some things. What I'm trying to do is calculate the count of "Ties" when the teams below have the same score. The 24 records below show randomized (okay not-so-randomized, but consider it to be constantly changing) data where I calculate a count of wins, losses and ties per team.

The logic for the top line of Count of Wins is: =COUNTIF($E$48:$E$71,B34) (then dragged down)
The logic for the top line of Count of Losses is: =COUNTIF($D$48:$D$71,B34) (then dragged down)

I've got the count of wins and losses taken care of in the formulas above, but I can't for the life of me figure out how to get the count of "Tie" when the games are tied.

Anyone have any clever ideas? The team name could be listed under team 1 or team 2 and then the winner could also be listed under team 1 or team 2. I have my macro exporting "Tie" to the winner and loser columns when the score is tied and I would like to use that to count.

	Team Name	Count of Wins	Count of Losses	Count of Ties
	Kalir Farms	0	2	2
	NiColoy	0	2	2
	NiCoForm	0	2	2
	www.yourcakery.com	2	0	2
	Sanz Electric	2	0	2
	Erb Financial	2	2	0
	Jay R. Gelb & Company Inc.	2	2	0
	Susan Bracker DDS	0	2	2
	Miniature Railways	0	2	2
	Rochester Home Equity, Inc.	2	0	2
	Veramark	2	0	2
	Apartment Finder Renters Guide	2	0	2
Record Number	Team 1	Team 2	Overall Loser	Overall Winner
1	Kalir Farms	Apartment Finder Renters Guide	Tie	Tie
2	NiColoy	Veramark	Tie	Tie
3	NiCoForm	Rochester Home Equity, Inc.	Tie	Tie
4	www.yourcakery.com	Miniature Railways	Tie	Tie
5	Sanz Electric	Susan Bracker DDS	Tie	Tie
6	Erb Financial	Jay R. Gelb & Company Inc.	Erb Financial	Jay R. Gelb & Company Inc.
7	Jay R. Gelb & Company Inc.	Erb Financial	Jay R. Gelb & Company Inc.	Erb Financial
8	Susan Bracker DDS	Sanz Electric	Susan Bracker DDS	Sanz Electric
9	Miniature Railways	www.yourcakery.com	Miniature Railways	www.yourcakery.com
10	Rochester Home Equity, Inc.	NiCoForm	NiCoForm	Rochester Home Equity, Inc.
11	Veramark	NiColoy	NiColoy	Veramark
12	Apartment Finder Renters Guide	Kalir Farms	Kalir Farms	Apartment Finder Renters Guide
13	Apartment Finder Renters Guide	Kalir Farms	Tie	Tie
14	Veramark	NiColoy	Tie	Tie
15	Rochester Home Equity, Inc.	NiCoForm	Tie	Tie
16	Miniature Railways	www.yourcakery.com	Tie	Tie
17	Susan Bracker DDS	Sanz Electric	Tie	Tie
18	Erb Financial	Jay R. Gelb & Company Inc.	Erb Financial	Jay R. Gelb & Company Inc.
19	Jay R. Gelb & Company Inc.	Erb Financial	Jay R. Gelb & Company Inc.	Erb Financial
20	Susan Bracker DDS	Sanz Electric	Susan Bracker DDS	Sanz Electric
21	Miniature Railways	www.yourcakery.com	Miniature Railways	www.yourcakery.com
22	Rochester Home Equity, Inc.	NiCoForm	NiCoForm	Rochester Home Equity, Inc.
23	Veramark	NiColoy	NiColoy	Veramark
24	Apartment Finder Renters Guide	Kalir Farms	Kalir Farms	Apartment Finder Renters Guide

<tbody>
</tbody>

 

[RonB1111]

Try:
=COUNTIFS($B$48:$B$71,B34,$D$48:$D$71,"Tie")+COUNTIFS($C$48:$C$71,B34,$D$48:$D$71,"Tie")

 

[thenapolitan]

Try:
=COUNTIFS($B$48:$B$71,B34,$D$48:$D$71,"Tie")+COUNTIFS($C$48:$C$71,B34,$D$48:$D$71,"Tie")

Works great! Thanks!

 

[RonB1111]

You're welcome!