Menu
Check for two out of three conditions (Homework)
Excel Challenges - 73 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.
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.
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.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Untrimmable Spaces – Excel Formula | Get rid of that ugly formatting with two simple tricks » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
73 Responses to “Check for two out of three conditions (Homework)”
Here's my attempt:
Only two trues
A1+B1+C1=2
Total number of 2 trues
=SUMPRODUCT(--((A1:A100+B1:B100+C1:C100)=2))
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?
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}
{=Sum(--(A1:C1))=2}
In Column E, the following formula:
=IF(COUNTIFS(A1:C1,TRUE)=2,TRUE,"")
Total No of TRUEs:
=COUNTIFS(E1:E100,TRUE)
=IF(COUNTIF(A1:C1,TRUE)=2,TRUE,"")
=WENN(SUMMENPRODUKT(--(A1:C1))=2;1;)
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.
Simpler than that:
=IF((A1=TRUE)+(A2=TRUE)+(A3=TRUE)=2,TRUE,FALSE)
THANK YOU SO MUCH!!! I have been looking for this formula for days! I had it years ago and forgot it, found it, then accidently deleted it from my document.
Simpler still, as MF says:
=IF(A1+A2+A3=2,TRUE,FALSE)
Exactly!
Actually you can skip the IF statement. 🙂
TRUE
This one is for who does not how to use SUMPRODUCT:
Answer:
=--(A1+B1+C1)=2
Bonus question:
=--(A1+B1+C1)>=2
Answer to main question: "=SUMPRODUCT(--$A2:$C2)=2"
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!
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?
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!
Try {1;1;1} instead of {1,1,1}
That did it. Thanks!
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.
Ah, same conclusion:)
Whole matrix: =SUM(N(MMULT(--A5:C11;{1;1;1})=2))
Row by row: =IFERROR(1/((A6+B6+C6)=2);"")
=(A1*1+B1*1+C1*1)=2
This seems to be the simplest one without containing any function.
=SI((A1*B1+C1)>0;VERDADERO;FALSO)
-----------------------
=IF((A1*B1+C1)>0,TRUE,FALSE)
=SI((A1*B1+C1)>0;VERDADERO;FALSO)
-----------------------
=IF((A1*B1+C1)>0,TRUE,FALSE)
Sorry, fail if 3 trues
=IF((A1*B1+C1)=1,TRUE,FALSE)
This is valid
Forgive me..
Is not my day...
=AND((A1*B1+C1)=1,(A1+B1*C1)=1)
=Y((A1*B1+C1)=1;(A1+B1*C1)=1)
YES...
=(COUNTIF(A1:C1,TRUE)=2)
Answer: =SUMPRODUCT(--A1:C1)=2
Bonus: =SUM(IF(MMULT(IF(A1:C100=TRUE,1,0),{1;1;1})=2,1,))
=IF(COUNTIF(A2:C2,TRUE)=2,TRUE,FALSE)
array formulas:
=COUNT(1/A1:C1)=2
=COUNT(MATCH(--A1:A100&--B1:B100&--C1:C100,{"011";"101";"110"},))
=SUM(--(--A1:A100&--B1:B100&--C1:C100={"011","101","110"}))
=SUM(--(A1:A100*100+B1:B100*10+C1:C100={11,101,110}))
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
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))
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!
Ed,
I believe you don't need If here. The formula result itself is TRUE OR FALSE 🙂
You are correct! Thank you Sagar.
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
Or course, you dont have to search for True
=((LEN(A1&B1&C1)-(LEN(SUBSTITUTE(A1&B1&C1,"T","")))))>1
Or just falses
=((LEN(A1&B1&C1)-(LEN(SUBSTITUTE(A1&B1&C1,"F","")))))<2
if values in range A1:C1 then {=SUM(A1:C1*{TRUE,TRUE,TRUE})=2} works fine. Gives true/false result
array formula
=SUM(--(COUNTIF(OFFSET(A1:C1,ROW(1:101)-1,),1=1)=2))
=SUM(N(FREQUENCY(A1:C100*ROW(A1:A100),ROW(A1:A100))=2))
correct:
=SUM(N(FREQUENCY(A1:C100*ROW(A1:A100),(ROW(A1:A100)-1))=2))
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","-")
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.
=COUNTIF(A1:C1,TRUE)>1
=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
=IF(COUNTIFS(A3:C3,"2")=2,"True","False")
=AND(OR(A1,B1,C1),NOT(XOR(A1,B1,C1)))
=SUMPRODUCT(--(((A1:A100=TRUE)+(A1:B100=TRUE)+(C1:C100=TRUE))=2))
correct range and removing the redundant true comparison
=SUMPRODUCT(--(((A1:A100)+(B1:B100)+(C1:C100))=2))
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.
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).
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!
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
You can try this.
=SUMPRODUCT((((a1:a10=b1:b10)+(a1:a10=c1:c10)+(c1:c10=b1:b10))=1)*1)
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!
=COUNT(1/(COUNTIF(OFFSET(A1:C1,ROW(A1:A100)-1,),A1:C100)=2))/2
Good solutions Robert & David.
I suggest this
=(a1=b1)+(b1=c1)+(c1=a1) = 1
=MAX(COUNTIF(A1:C1,A1:C1))=2
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!
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...
oops... Just saw Sagar's post 🙂
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.
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!
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
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")
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