For a complete view of what I am trying to do: I am attempting to make a matrix containing the results of head-to-head sports matchups.
In doing so, I am attempting to use a formula to look at numerous pieces of information, but in the end what it is supposed to do is identify when a given team plays another given team, and to count when a specific result took place.
As for the specific problem at hand: I've already developed a formula using a COUNTIFS that counts the number of times two teams plays each other:
=COUNTIFS($F:$F,$Q5,$J:$J,$R$3)+COUNTIFS($J:$J,$Q5,$F:$F,$R$3)
Column F represents a column containing all of the Teams for all of the games
Column J represents the corresponding column containing all of the opponents for all of the games
Q5 represents a specific team in the matrix
R3 represents a specific team (Q5's opponent in the matrix)
In plain language, I am saying COUNT IF (Q5 is found in column F and R3 is found in column J), OR (Q5 is found in column J and R3 is found in column F).
What I want to do now, however, is to add another component. I want to add another formula so it says COUNT IF the result of the match is favorable to a specific team (Column O is equal to Q5). The formula that I came up with is:
=COUNTIFS($O:$O,Q5)*AND(COUNTIFS($F:$F,$Q5,$J:$J,$R$3)+COUNTIFS($J:$J,$Q5,$F:$F,$R$3))
But it does not work, as it returns EVERY time Q5 is found in column O, not just the times when Q5 and and R3 are found in either columns F or J [returns a value of 9, when it should be just 2].
To me, my formula says: COUNT IF Q5 is found in Column O AND ((IF Q5 is in column F and R3 is in Column J) OR (IF Q5 is in Column J and R3 is in Column F)).
But, it seems like that's not the case.
Can anyone help me out?
Bookmarks