Check for two out of three conditions (Homework)

Posted on January 13th, 2017 in Excel Challenges - 72 comments

Time for some logic check.

Suppose, you have three logic values in A1:C1 (TRUE or FALSE values in each cell)

You need to find out if ONLY two of these values are TRUE.

two-out-of-three-logic

How would you write the formula?

Got an answer? Awesome. Just post your formula in the comments. Let’s see how much variety we can get from all of our readers.

Bonus question: Let’s say you have a list A1:C100, each row containing outcome of three conditions. How do you find out the total number of rows where ONLY two out of three conditions are met.

Go ahead and post your answers.

Related: XOR formula in Excel | Summing up neither A nor B values.

Introducing our Online Power BI Class:

Introducing Online Power BI Training from chandoo.org - check it out today

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

Click here to know more and join us.

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

72 Responses to “Check for two out of three conditions (Homework)”

  1. MF says:

    Here's my attempt:

    Only two trues
    A1+B1+C1=2

    Total number of 2 trues
    =SUMPRODUCT(--((A1:A100+B1:B100+C1:C100)=2))

    • jomili says:

      Curious: if I put in A1 + B1 +C1, I get 2 for the answer, but if I put in Sum(A1:C1) I get 0. Why does SUM work differently?

      • jomili says:

        Found my own answer in the documentation on the SUM function:

        If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.

        But we could do this:
        {=Sum(--)A1:C1))=2}

  2. TheQ47 says:

    In Column E, the following formula:
    =IF(COUNTIFS(A1:C1,TRUE)=2,TRUE,"")

    Total No of TRUEs:
    =COUNTIFS(E1:E100,TRUE)

  3. Amol says:

    =IF(COUNTIF(A1:C1,TRUE)=2,TRUE,"")

  4. ab says:

    =WENN(SUMMENPRODUKT(--(A1:C1))=2;1;)

  5. Eric says:

    For the first part (i.e., return TRUE if 2 of 3 are TRUE:

    =SUMPRODUCT(--(A1:C1))=2

    The easy way for the second part is to put the above formula in D1 and copy down, then put this in E1

    =SUMPRODUCT(--D1:D100)

    MF's way for part 2 is the single cell (no copy down) answer.

  6. Richard Charrington says:

    Simpler than that:

    =IF((A1=TRUE)+(A2=TRUE)+(A3=TRUE)=2,TRUE,FALSE)

  7. Richard Charrington says:

    Simpler still, as MF says:

    =IF(A1+A2+A3=2,TRUE,FALSE)

  8. Alex says:

    This one is for who does not how to use SUMPRODUCT:

    Answer:
    =--(A1+B1+C1)=2

    Bonus question:
    =--(A1+B1+C1)>=2

  9. Bra?o Kollár says:

    Answer to main question: "=SUMPRODUCT(--$A2:$C2)=2"

  10. John Jairo V says:

    Hi to all!

    Check if two conditions are true:
    =A1+B1+C1=2 (like MF)

    Get Total number of rows with two conditions met:
    =SUM(N(MMULT(--A1:C100,{1,1,1})=2))

    Blessings!

    • jomili says:

      John,
      I see you and Ola used the same MMULT formula. I'm trying it in my worksheet and getting a #VALUE error. My range of TRUE or FALSE is A5:C100, so the formula I'm using is
      =SUM(N(MMULT(--A5:C100,{1,1,1})=2))

      I'm NOT CSE entering it (though I tried that too). Any ideas why I'd get the Value error?

    • Sean Hodkinson says:

      John Jairo, thank you for this great MMULT formula, I spent 3 hours this evening figuring out how to use it, should come in handy in future.

  11. Ola says:

    Ah, same conclusion:)
    Whole matrix: =SUM(N(MMULT(--A5:C11;{1;1;1})=2))
    Row by row: =IFERROR(1/((A6+B6+C6)=2);"")

  12. Mehmet Gunal OLCER says:

    =(A1*1+B1*1+C1*1)=2

  13. Juan says:

    =SI((A1*B1+C1)>0;VERDADERO;FALSO)
    -----------------------
    =IF((A1*B1+C1)>0,TRUE,FALSE)

  14. Juan says:

    =SI((A1*B1+C1)>0;VERDADERO;FALSO)
    -----------------------
    =IF((A1*B1+C1)>0,TRUE,FALSE)

    Sorry, fail if 3 trues

  15. Juan says:

    =IF((A1*B1+C1)=1,TRUE,FALSE)

    This is valid

  16. Juan says:

    Forgive me..
    Is not my day...

  17. Stupid Juan says:

    =AND((A1*B1+C1)=1,(A1+B1*C1)=1)

    =Y((A1*B1+C1)=1;(A1+B1*C1)=1)

    YES...

  18. Nagaraj says:

    =(COUNTIF(A1:C1,TRUE)=2)

  19. Jason Morin says:

    Answer: =SUMPRODUCT(--A1:C1)=2

    Bonus: =SUM(IF(MMULT(IF(A1:C100=TRUE,1,0),{1;1;1})=2,1,))

  20. Guy S says:

    =IF(COUNTIF(A2:C2,TRUE)=2,TRUE,FALSE)

  21. ZORRO2005 says:

    array formulas:
    =COUNT(1/A1:C1)=2
    =COUNT(MATCH(--A1:A100&--B1:B100&--C1:C100,{"011";"101";"110"},))

  22. ZORRO2005 says:

    =SUM(--(--A1:A100&--B1:B100&--C1:C100={"011","101","110"}))

  23. ZORRO2005 says:

    =SUM(--(A1:A100*100+B1:B100*10+C1:C100={11,101,110}))

  24. Jack says:

    I did:

    {=SUM(--(A1:C1)=2}

    Then if you don't have TRUE or FALSE in those cells but you want to find a particular entry instead:

    {=SUM(--(A1:C1="A"))=2}

    Range can be extended as far as you like and you can manipulate the =2 to say >3 or <=20 depending on how many TRUEs you want to find

  25. Xiq says:

    It is of course all in binary, so binary compensation it is! 😉

    Answer:
    =--DEC2BIN(A1+B1+C1)=10

    Bonus:
    =SUMPRODUCT(--(--DEC2BIN(A1:A100+B1:B100+C1:C100)=10))

  26. Ed B says:

    Here is my attempt:

    =IF(AND(OR(A1:C1),NOT(XOR(A1:C1))),TRUE,FALSE)

    I have to give more thought to the bonus question!

  27. deesloop says:

    First post 🙂

    Manipulating as a string is an unpleasant way of doing it m

    ((LEN(A1&B1&C1)-(LEN(SUBSTITUTE(A1&B1&C1,"TRUE",""))))/4)>1

  28. deesloop says:

    Or just falses

    =((LEN(A1&B1&C1)-(LEN(SUBSTITUTE(A1&B1&C1,"F","")))))<2

  29. Hinck says:

    if values in range A1:C1 then {=SUM(A1:C1*{TRUE,TRUE,TRUE})=2} works fine. Gives true/false result

  30. ZORRO2005 says:

    array formula
    =SUM(--(COUNTIF(OFFSET(A1:C1,ROW(1:101)-1,),1=1)=2))

  31. ZORRO2005 says:

    =SUM(N(FREQUENCY(A1:C100*ROW(A1:A100),ROW(A1:A100))=2))

  32. Bhavani Seetal says:

    Hi All,
    A1 B1 C1 LOGIC
    TRUE TRUE FALSE Any 2 Values are True
    FALSE TRUE FALSE -
    TRUE TRUE FALSE Any 2 Values are True
    FALSE TRUE FALSE -
    TRUE TRUE TRUE -
    Formula:
    =IF(COUNTIF(A2:C2,TRUE)=2,"Any 2 Values are True","-")

  33. govind says:

    Hi Chandoo

    i have been looking for some solution on margin % bridge and tried lot of searches but could not succeed, was hoping if you could provide me some solution, below is the problem, if i want to map the movement in margin% from one period to another in terms of contribnution from price, volume, cost changes, what is solution

    Margin in 2015 10%
    Margin in 2016 12%

    how these 2% movement can be broken into price variance, volume and cost mix variance.

  34. Chirayu says:

    =COUNTIF(A1:C1,TRUE)>1

  35. mark says:

    =IF(SUM(1*(A1:C1))=2,1,0)

    for total number of rows with exactly 2 TRUE values, just sum the formulas, or, more simply, another array formula

  36. SK says:

    =IF(COUNTIFS(A3:C3,"2")=2,"True","False")

  37. Sagar Malik says:

    =AND(OR(A1,B1,C1),NOT(XOR(A1,B1,C1)))

  38. Sagar Malik says:

    =SUMPRODUCT(--(((A1:A100=TRUE)+(A1:B100=TRUE)+(C1:C100=TRUE))=2))

  39. Jomili says:

    Just a comment that most of these responses check whether there are two trues in the range, whereas the post title is "Check for two out of three conditions ", so instead of True/Falses the data should be other values. For instance "Bill, Tom, Bill" would be true, "Bill, Tom, Mary" would be false. I'd like to see some solutions for that type of problem.

    • Robert says:

      Jomili,
      Great complication to this problem! How about:

      =OR(COUNTIF(A1:C1,A1)=2,COUNTIF(A1:C1,B1)=2,COUNTIF(A1:C1,C1)=2)

      I love all the posts in here - always amazes me how many solutions to the problem there are (and mine usually ends up being way to complicated).

      • Jomili says:

        Robert,
        You rock! I thought David Hager's CSE formula was going to be the only that would work, then you go ahead and do it with a simple (though it LOOKS complicated) formula. Way to go!

        • Jomili says:

          So, applying Robert's solution to the grid below (sorry, this application doesn't allow me to paste very well), I show 8 rows have 2 values that have 2 out of 3 conditions the same. Robert's formula will show the rows that match on a row by row basis, but going to Chandoo's Bonus question, How do you find out the total number of rows where ONLY two out of three conditions are the same?

          TRUE FALSE Blue
          Ted Bill Ted
          TRUE FALSE FALSE
          Mary Tom Mary
          TRUE FALSE FALSE
          FALSE TRUE TRUE
          Ted Bill Ted
          FALSE TRUE TRUE
          TRUE FALSE TRUE
          Mary Bill Ted

    • Chandoo says:

      Good solutions Robert & David.

      I suggest this

      =(a1=b1)+(b1=c1)+(c1=a1) = 1

  40. David Hager says:

    =MAX(COUNTIF(A1:C1,A1:C1))=2

    • Jomili says:

      I had to CSE it to get it to work, but it DOES work fine, gives TRUE if any two of three values are the same. I think Chandoo should give you the prize!

  41. Vinu says:

    Has anybody tried =AND(NOT(XOR(A1:C1)),OR(A1:C1)) ?
    Worked for me

    Seems like this would work for checking for a majority TRUE in any length array...

  42. jomili says:

    Zorro,

    I can't get your formula to work. Walking through it, it looks like "A1:C100" doesn't resolve to a value (gives VALUE error), which is hosing the formula.

  43. jomili says:

    Zorro,
    Okay, I figured it out:
    1) Have to enter it as array (CSE)
    2) If my values don't start in A1:C1, but instead in A10:C10 through A19:C19, have to change the formula like so:
    {=COUNT(1/(COUNTIF(OFFSET(A10:C10,ROW(A1:A9)-1,),A10:C19)=2))/2}
    Appears to work fine. Thanks!

  44. Sadanandan says:

    I want a formula ie. If a1=1 and b1 should be equal to c1 and if a1 is 2 b1 should be equal to d1

  45. Pixy says:

    The problem is if you do not have numbers. Thats why I think I need logical formula.
    What about this?
    =IF(AND(OR(A1=B1;B1=C1;A1=C1));"True";"False")

  46. Ashok says:

    i want to check whether a number is in range a1:e1. suppose if that number is in cell c1, i want to find the largest number in range c1:c10. wats the formula in excel

Leave a Reply