Compare two cells and count how many times either one exceeds 500

Here is my quandary.

I have a score sheet. It has two team on it. I would like to automatically count how many times a team exceeds 500 and give that team a win. It also needs to give the team with the higher total a win if they both exceed 500.

I have attached a sample sheet. The cells i am looking to calculate is the wins and loss column to the right.

Any help would be greatly appreciated.

Los

I was trying to use countif but i couldn't figure out how to get my desired results, or even if that was the correct path to go down....


At a loss

Los

Team 1 Wins

=COUNTIF($C:$C,">500")

Team 2 wins

=COUNTIF($E:$E,">500")

will that account for both teams scoring over 500 but yielding only the team with the largest number over 500 the winner?


Example
Team 1 515 points
Team 2 534 Points

in this scenario they are both over 500 but team two is the winner

Thanks for the clarification

Los

Maybe:

Team 1 wins/Team 2 loses (I2 & J3):
=SUMPRODUCT(--($C$3:$C$50>$E$3:$E$50),--($C$3:$C$50>=500))

Team 2 wins/Team 1 loses (I3 & J2):
=SUMPRODUCT(--($C$3:$C$50<$E$3:$E$50),--($E$3:$E$50>=500))

If there are more games, you can increase the size of the range (currently through row 50)

So i tried adapting that formula to my sheet and i cannot figure out what is wrong. I have attached my actual sheet and am looking for a way to automatically count wins for each team. A win being a the highest total over 500 points for each game.

Thanks in advance for all of the help

Los

Your "numbers" in columns Q and AH look like numbers, but they're actually text. Tack a "+0" onto the back of your formulas in those columns to massage them into being actual numbers. For example, Q3 should become:

=IF(A3="","",(LEFT(P3,LEN(P3)-1)&K3)+0)

Do that for both columns and the Win/Loss totals will be correct. See attached:

For a better overall table, change the formulas in Q and AH so that they use mathematical operators like "+" and "-" rather than textual operators like "&". You could replace the formula in Q3 with:

=IF(A3="","",P3+K3)

...and end up with a number instead of text that looks like a number.

Thank you so much i woulda never figured that out.....

Los

Change the calculations in Q and Ah

=IF(A3="","",INT(P3/10)*10+K3)

=IF(R3="","",(INT(AG3/10)*10+AB3))

Your calculations result in TEXT field and hence the spurious results.

Glad to help, good luck!