Forum Discussion
Solved
Counting matching cells
Hello. I have a spreadsheet with some football results on, where each week, each player is either on red team or blue team. At the moment I am using the following formula to count how many times two players have been on the red team together:
=SUMPRODUCT(ISEVEN(COLUMN(Sheet2!A:AA))*(((Sheet2!A$3:AA$3="Red")*(Sheet2!A4:AA4="Red"))))
However, when I try to alter this formula to count how many times two players have won together on the red team (by adding an addition check that B$30:AA$30="Red") doesn't seem to work. This is my latest attempt:
=SUMPRODUCT((ISEVEN(COLUMN(Sheet2!A:AQ))*((Sheet2!A$3:AQ$3="Red")*(Sheet2!A5:AQ5="Red")))*(Sheet2!B$30="Red"))
I essentially want to check if Player A, Player B and the Winner are all red, each week, then count the number of times this happens.
Any ideas how I can do this? Should I change my original formula? All suggestions welcome!
Since the odd-numbered columns in the player rows will never contain Blue or Red, you can use the simpler formula
=COUNTIFS(Sheet2!A$3:AA$3,"Red",Sheet2!A4:AA4,"Red")
to count the number of times the players in rows 3 and 4 both played for red. And the number of times they both played for red AND red won:
=COUNTIFS(Sheet2!A$3:AA$3,"Red",Sheet2!A4:AA4,"Red",Sheet2!B$30:AB$30,"Red")
Since the odd-numbered columns in the player rows will never contain Blue or Red, you can use the simpler formula
=COUNTIFS(Sheet2!A$3:AA$3,"Red",Sheet2!A4:AA4,"Red")
to count the number of times the players in rows 3 and 4 both played for red. And the number of times they both played for red AND red won:
=COUNTIFS(Sheet2!A$3:AA$3,"Red",Sheet2!A4:AA4,"Red",Sheet2!B$30:AB$30,"Red")
- Thank you for your help
