CODEWITHSUNDEEP
excelexcel-formula
Norrec
Norrec

Reputation: 3

Multiple AND/IFS in excel

I am currently facing a problem if I add one more criteria to my formula. I created a tracker to measure a status below:

=IF((AND(A1<>"",B2<>"")),"Closed","Open")

What this does:
if A1 contains a value, and B2 is empty = Open
if both contain value = Closed
if both are empty = Open

I tried to insert one more criteria, but the formula doesn't work.

I am looking for a solution to return this:
If all are blank = Output 1
If A1 contains a value, B2 is blank and C2 is blank = Output 2
If A1 contains a value, B2 contains a value, C2 blank = Output 3
If all contain a value = Output 4

Apreciate your guidance!

Upvotes: 0

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

You can do this with nested if():

=if(and(a1="",b2="",c2=""), output1,
    if(and(b2="", c2=""), output2,
       if(c2="", output3, output4)))

The logic goes as:

  • The first condition tests if all three are blank.
  • If not, the second condition tests for b2 and c2. If these are blank, then a1 must not be (otherwise it would be caught by the first condition).
  • The third only needs to check for c2 to distinguish between output3 and output4.

Upvotes: 1

Related Questions