Explanation
The goal in this example is to calculate total wins, losses, and ties for each team listed in column G. The problem is complicated somewhat by the fact that a team can appear in either column B or C, so we need to take this into account when calculating wins and losses. For convenience and readability only, the formula uses the following named ranges: team1 (B5:B14), team2 (C5:C14), score1 (D5:D14), and score2 (E5:E14).
In solving this problem, you might think of using the COUNTIF or COUNTIFS function, but these functions are limited to working with existing ranges for criteria. Instead, the formula in the example uses the SUMPRODUCT function to sum the result of an array expression based on Boolean logic. Inside SUMPRODUCT on the left we have an expression to count wins when a team appears in column B:
((team1=$G5)*(score1>score2))
If we replace the mixed reference $G5 with the value in G5, we have:
((team1="Red")*(score1>score2))
This expression is actually formed from two expressions, joined by multiplication (*) to create AND logic. The expression on the left checks if team1 is "Red":
(team1="Red") // check team is "Red"
The expression on the right checks if score1 is greater than score2:
(score1>score2) // check score1 greater than score2
Because both expressions involve ranges with multiple values, they each return arrays that contain multiple results. Rewriting the formula with the arrays returned, we have:
({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} *
{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE})
The math operation of multiplication (*) works like AND in Boolean algebra and also coerces the TRUE and FALSE values into 1s and 0s. When corresponding values are both TRUE, the result is 1. In any other case, the result is 0. The result is a single array like this:
{0;0;1;0;0;0;0;0;0;0}
Note the third value is 1, which corresponds to game 3, where Red beats Black 3-2. At this point, we have collected an array that represents all Red victories when Red is Team1. This covers the left side of the logic inside SUMPRODUCT.
On the right side, we have similar logic that checks for Red wins when Read is Team2:
((team2=$G5)*(score2>score1))
This logic evaluates in the same way as the left logic. Summarized, we have:
((team2="Red")*(score2>score1))
Then:
({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*({FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE})
Then:
({0;0;0;0;0;0;0;0;0;0})
Since Red doesn't actually appear as Team2 in any game, all results are zero.
With both the left and right sides evaluated, we can now rewrite the original formula like this:
=SUMPRODUCT(({0;0;1;0;0;0;0;0;0;0})+({0;0;0;0;0;0;0;0;0;0}))
At this point notice the math operator between the two arrays is addition (+), which corresponds to OR logic. We do this because we are counting Red wins as Team1 OR Team2. The result is a single array inside SUMPRODUCT:
=SUMPRODUCT({0;0;1;0;0;0;0;0;0;0})
With just one array to process, SUMPRODUCT sums the items in the array and returns a final result, 1.
For comparison, if we solve for the Green team in cell H7, we get 4:
=SUMPRODUCT(((team1=$G7)*(score1>score2))+((team2=$G7)*(score2>score1)))
Then:
=SUMPRODUCT(({0;0;0;0;0;0;0;1;1;0})+({0;1;0;0;1;0;0;0;0;0}))
Then:
=SUMPRODUCT({0;1;0;0;1;0;0;1;1;0}) // returns 4
Counting losses
The formula in I5 to count losses is very similar:
=SUMPRODUCT(((team1=$G5)*(score1<score2))+((team2=$G5)*(score2
The only difference is the "reversed" logic in checking scores.
Counting ties
The formula to count follows the same pattern, the only difference is we check for equal scores:
=SUMPRODUCT(((team1=$G5)*(score1=score2))+((team2=$G5)*(score2=score1)))
One problem with this formula is that games without scores are counted as ties. This happens because Excel evaluates empty cells as zero, so when score1 and score2 are empty, they appear to the formula as zero, which counts like a 0-0 tie. To work around this problem, you can add a second array to SUMPRODUCT to check for ties with logic based on the LEN function:
(LEN(score1)>0)*(LEN(score2)>0) // check for no score
When both score1 and score2 have values, the result is 1. When either score is empty, the result is zero, and this zero "cancels out" the false tie that occurs when both cells are empty. The complete formula is:
=SUMPRODUCT(
((team1=$G5)*(score1=score2))+
((team2=$G5)*(score2=score1)),
(LEN(score1)>0)*(LEN(score2)>0)
)
The LEN function is checking for any characters. When LEN returns zero, it means a cell is empty.
Related formulas
