 # Check for two out of three conditions (Homework)

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. 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. Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

### Related articles:

 Written by Chandoo Tags: excel formulas, homework, Learn Excel, logical operators in excel, XOR 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}

• jomili says:

{=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)

• Nils says:

Exactly!

• MF says:

Actually you can skip the IF statement. 🙂

• Richard says:

TRUE

8. Alex says:

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

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

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

9. Bra?o Kollár says:

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?

• John Jairo V says:

Sorry!

When I translate the formula, I made a mistake in matrix constant separator.

Must be:
=SUM(N(MMULT(--A5:C100,{1;1;1})=2))

In English
Column Separator is ","
Row Separator is ";"

In Latin Spanish (Colombia)
Column Separator is "\"
Row Separator is ";"

Blessings!

• Leonid says:

• jomili says:

That did it. Thanks!

• 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

• Mehmet Gunal OLCER says:

This seems to be the simplest one without containing any function.

13. Juan says:

-----------------------
=IF((A1*B1+C1)>0,TRUE,FALSE)

14. Juan says:

-----------------------
=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:

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! 😉

=--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!

• Sagar Malik says:

Ed,
I believe you don't need If here. The formula result itself is TRUE OR FALSE 🙂

• Ed B says:

You are correct! Thank you Sagar.

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

• deesloop says:

Or course, you dont have to search for True

=((LEN(A1&B1&C1)-(LEN(SUBSTITUTE(A1&B1&C1,"T","")))))>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))

• ZORRO2005 says:

correct:
=SUM(N(FREQUENCY(A1:C100*ROW(A1:A100),(ROW(A1:A100)-1))=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))

• Sagar Malik says:

correct range and removing the redundant true comparison

=SUMPRODUCT(--(((A1:A100)+(B1:B100)+(C1:C100))=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:

You can try this.

=SUMPRODUCT((((a1:a10=b1:b10)+(a1:a10=c1:c10)+(c1:c10=b1:b10))=1)*1)

• Jomili says:

Chandoo,
You're a wizard! That appears to work wonderfully! But now you're going to force me into learning more about Sumproduct, one formula I've never truly understood. Thanks!

• ZORRO2005 says:

=COUNT(1/(COUNTIF(OFFSET(A1:C1,ROW(A1:A100)-1,),A1:C100)=2))/2

• 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...

• Vinu says:

oops... Just saw Sagar's post 🙂

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.
=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

 « Untrimmable Spaces – Excel Formula Get rid of that ugly formatting with two simple tricks »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.