I keep the data for the Debate program – wins, losses, etc. I wanted to see how often the team that called the coin toss and won the coin toss, then went on to win the game. Should be simple, but it was very challenging.
To compare how often a team called the coin toss and won the coin toss was relatively easy.
For example, in this image, column A is coin toss caller and column D is coin toss winner. There were three times that a coin toss caller was a coin toss winner. (Bob, Phil, and Jane). The formula to compute this is absolutely convoluted, but everyone on the Net seemed to know it.
=SUMPRODUCT(–(A2:A5=D2:D5))
Not that I could come up with that formula on my own, mind, just that the ‘Net is awash with easy-to-follow formulae. I Googled “excel count number of matching values between two columns”and got quick satisfaction
Two columns was easy. Three columns, impossible.
I searched through Google with so many phrases and got just the worst results.
- count matches between three columns
- how to match values in nonadjacent columns
- count matches between three ranges
- how to match values in non consecutive columns
- excel counting + matching 3 columns
- count matches between 3 columns in excel
- how do I compare three columns in Excel for matches?
- how do I compare three columns in Excel for matches using sumproduct?
- how to Compare Three Columns and Return a Value in Excel
- count matches between 3 columns in excel (I had no better luck the second time I tried this)
Finally I emailed the expert, Loren, the CEO from Evolving Technologies Corporation, who knows all there is to know about Excel. He had the formula!
=SUMPRODUCT(–(A2:A5=D2:D5),–(D2:D5=H2:H5)).
So now I know that two teams called the coin toss, won the coin toss, and then won the game (Bob and Phil)
P.S. Also, I submitted my question to Stack Overflow website and got a different formula, which also works, though it appears to be more complicated than the one above:
=SUMPRODUCT((A2:A5=D2:D5)*(A2:A5=H2:H5),A2:A5<>"")
Occasional Observations of Traveling Technology Coach 