+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS using both AND and OR components

  1. #1
    Registered User
    Join Date
    03-26-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2010
    Posts
    2

    COUNTIFS using both AND and OR components

    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?

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: COUNTIFS using both AND and OR components

    because you are using countifs, you can add it in as an aditional criteria like this:

    =COUNTIFS($F:$F,$Q5,$J:$J,$R$3,$O:$O,Q5)+COUNTIFS($J:$J,$Q5,$F:$F,$R$3,$O:$O,Q5)
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,699

    Re: COUNTIFS using both AND and OR components

    Presumably the team shown in column O must be one of the teams shown in F or J (for each row)? In which case this should work

    =COUNTIFS($J:$J,$R$3,$O:$O,$Q5)+COUNTIFS($F:$F,$R$3,$O:$O,$Q5)
    Audere est facere

  4. #4
    Registered User
    Join Date
    03-26-2012
    Location
    Iowa
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: COUNTIFS using both AND and OR components

    I used this solution. It is extremely simple and effective.

    I wasn't able to see that solution, but once I read it, it was obvious that it would work.

    Thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1