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.


[TABLE="width: 761"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]Team Name[/TD]
[TD="align: center"]Count of Wins[/TD]
[TD="align: center"]Count of Losses[/TD]
[TD="align: center"]Count of Ties[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Kalir Farms[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]NiColoy[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]NiCoForm[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]www.yourcakery.com[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Sanz Electric[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Erb Financial[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Jay R. Gelb & Company Inc.[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Susan Bracker DDS[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Miniature Railways[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Rochester Home Equity, Inc.[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Veramark[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Apartment Finder Renters Guide[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]

[/TD]
[/TR]
[TR]
[TD="align: center"]Record Number[/TD]
[TD="align: center"]Team 1[/TD]
[TD="align: center"]Team 2[/TD]
[TD="align: center"]Overall Loser[/TD]
[TD="align: center"]Overall Winner[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Kalir Farms[/TD]
[TD="align: center"]Apartment Finder Renters Guide[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]NiColoy[/TD]
[TD="align: center"]Veramark[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]NiCoForm[/TD]
[TD="align: center"]Rochester Home Equity, Inc.[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]www.yourcakery.com[/TD]
[TD="align: center"]Miniature Railways[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Sanz Electric[/TD]
[TD="align: center"]Susan Bracker DDS[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Erb Financial[/TD]
[TD="align: center"]Jay R. Gelb & Company Inc.[/TD]
[TD="align: center"]Erb Financial[/TD]
[TD="align: center"]Jay R. Gelb & Company Inc.[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Jay R. Gelb & Company Inc.[/TD]
[TD="align: center"]Erb Financial[/TD]
[TD="align: center"]Jay R. Gelb & Company Inc.[/TD]
[TD="align: center"]Erb Financial[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Susan Bracker DDS[/TD]
[TD="align: center"]Sanz Electric[/TD]
[TD="align: center"]Susan Bracker DDS[/TD]
[TD="align: center"]Sanz Electric[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Miniature Railways[/TD]
[TD="align: center"]www.yourcakery.com[/TD]
[TD="align: center"]Miniature Railways[/TD]
[TD="align: center"]www.yourcakery.com[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Rochester Home Equity, Inc.[/TD]
[TD="align: center"]NiCoForm[/TD]
[TD="align: center"]NiCoForm[/TD]
[TD="align: center"]Rochester Home Equity, Inc.[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]Veramark[/TD]
[TD="align: center"]NiColoy[/TD]
[TD="align: center"]NiColoy[/TD]
[TD="align: center"]Veramark[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]Apartment Finder Renters Guide[/TD]
[TD="align: center"]Kalir Farms[/TD]
[TD="align: center"]Kalir Farms[/TD]
[TD="align: center"]Apartment Finder Renters Guide[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]Apartment Finder Renters Guide[/TD]
[TD="align: center"]Kalir Farms[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]Veramark[/TD]
[TD="align: center"]NiColoy[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]Rochester Home Equity, Inc.[/TD]
[TD="align: center"]NiCoForm[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]Miniature Railways[/TD]
[TD="align: center"]www.yourcakery.com[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]Susan Bracker DDS[/TD]
[TD="align: center"]Sanz Electric[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]Erb Financial[/TD]
[TD="align: center"]Jay R. Gelb & Company Inc.[/TD]
[TD="align: center"]Erb Financial[/TD]
[TD="align: center"]Jay R. Gelb & Company Inc.[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]Jay R. Gelb & Company Inc.[/TD]
[TD="align: center"]Erb Financial[/TD]
[TD="align: center"]Jay R. Gelb & Company Inc.[/TD]
[TD="align: center"]Erb Financial[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]Susan Bracker DDS[/TD]
[TD="align: center"]Sanz Electric[/TD]
[TD="align: center"]Susan Bracker DDS[/TD]
[TD="align: center"]Sanz Electric[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]Miniature Railways[/TD]
[TD="align: center"]www.yourcakery.com[/TD]
[TD="align: center"]Miniature Railways[/TD]
[TD="align: center"]www.yourcakery.com[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"]Rochester Home Equity, Inc.[/TD]
[TD="align: center"]NiCoForm[/TD]
[TD="align: center"]NiCoForm[/TD]
[TD="align: center"]Rochester Home Equity, Inc.[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"]Veramark[/TD]
[TD="align: center"]NiColoy[/TD]
[TD="align: center"]NiColoy[/TD]
[TD="align: center"]Veramark[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"]Apartment Finder Renters Guide[/TD]
[TD="align: center"]Kalir Farms[/TD]
[TD="align: center"]Kalir Farms[/TD]
[TD="align: center"]Apartment Finder Renters Guide[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!