Excel - If condition with three values

Question

I would like to check three cells, and if the first two are "Yes" then put text in a specific cell, and if all three are "Yes" then different text in that specific cell.

Example:

Yes | Yes | No  | "Sort of Working"
Yes | Yes | Yes | "Working
No  | No  | Yes | "Not working"

Basically, if all three say Yes then it is "Working" if not, then it is "Not working" by using a formula

Thank you

I actually tried: =IF(E2="Yes","Not Working","")*IF(F2="Yes","Working","")*IF(G2="Yes","Working","") — Dominic
– Dominic, Commented Jul 7, 2015 at 22:04
So for your cells, I'd do =IF(AND(E2="Yes",F2="Yes",G2="Yes"),"Working", "Not Working") — Kaz
– Kaz, Commented Jul 7, 2015 at 22:06

score 2 · Accepted Answer · 2015-07-07 22:26:20Z

2

Three cells would be about my limit on stacking conditions within an AND function. Any more and I would perform a conditional count (COUNTIF function) and compare the number.

=IF(COUNTIF(A1:A3, "Yes")=3, "Working", "Not Working")

Alternate:

=LOOKUP(COUNTIF(A1:A3, "Yes"), {0,1,2,3}, {"Not Working","Not Working","Sort of Working","Working"})
=IFERROR(CHOOSE(COUNTIF(A1:A3, "yes")-1, "Sort of Working","Working"), "Not Working")

edited Jul 7, 2015 at 22:26

answered Jul 7, 2015 at 22:13

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

Dominic Over a year ago

I really like your LOOKUP formula you have. It looks like it will definitely work (theoretically), but I am getting an error trying to use it...

Dominic Over a year ago

Thank you @Jeeped ! The =IFERROR formula worked 100%

user4039065 Over a year ago

I had a round bracket in place of a curly brace in my first post. A subsequent edit fixed that. I've also added a CHOOSE function alternative.

Mr. Llama · Accepted Answer · 2015-07-07 22:03:12Z

0

You can nest AND() inside of IF().

=IF( AND(A1="Yes", A2="Yes", A3="Yes"), "Working", "Not Working" )

answered Jul 7, 2015 at 22:03

Mr. Llama

21k3 gold badges70 silver badges121 bronze badges

3 Comments

Dominic Over a year ago

Thank you @Mr.Llama . Do you know if I can add an OR statement in with the IF AND statement by any chance? To create what I have edited above in the original post.

Mr. Llama Over a year ago

Make two different cells. One checks the first two columns, one checks all three. AND() takes any number of comparisons, so add or remove comparisons as appropriate.

Dominic Over a year ago

Sadly I cannot use more columns then what I already have. It will be used to import into a website, and while I created a Java program for something else in this the formula is just something that has to come from Excel

Collectives™ on Stack Overflow

Excel - If condition with three values

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related