Double nested IF statement formula in Excel

Question

Good morning,

I would like to set a multiple if statement in Excel.

In row 8 you can see the following formula, which gives an answer in column Q. The answer based on this nested formula is Yes or No.

In row 13 all the cells are empty. Because the nested formula has been dragged down it still shows "No".

I was trying to write some double nested IF statement for this, which could cover the situation when the cells are empty and the Q column can come empty as well as a result.

   =IF(OR(M9=" ",OR(N9=" ",OR(O9=" ",OR(P9=" "(IF(OR(M9="No", OR(N9="No", OR(O9="No",OR(P9="No")))))),"Yes","No")," ")

Unfortunately, the formula is not working.

Is it a possibility to add up an additional condition for the nested IF statement in Excel?

I would start by changing your existing formula to this, as OR() takes multiple arguments: =IF(OR(M9="",N9="",O9="",P9="",M9="No", N9="No", O9="No",P9="No"),"No","Yes") — Solar Mike
– Solar Mike, Commented Jun 12, 2020 at 9:12
In my case, the OR must take every single argument separately, because I am getting No instead of Yes, which I need for this particular situation. — Geographos
– Geographos, Commented Jun 12, 2020 at 9:15
Just also note you are currently checking for a space instead of empty values. — JvdV
– JvdV, Commented Jun 12, 2020 at 9:18

JvdV · Accepted Answer · 2020-06-12 09:15:13Z

2

Something like:

=IF(COUNTA(M2:P2),<YourFormula>,"")

Where <YourFormula> is a placeholder for whatever formula you are interested in when any value in M2:P2 has a value.

edited Jun 12, 2020 at 9:15

answered Jun 12, 2020 at 9:09

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

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

2 Comments

Geographos Over a year ago

I wrote this: =IF(COUNTA(M9:P9=0),(OR(M9="No", OR(N9="No", OR(O9="No",OR(P9="No")))),"Yes","No")," ") But excel says, that I put too few arguments for this formula

JvdV Over a year ago

I'm not sure what it is you are after, but you are missing an IF, also you have copied what I showed wrongly. I didn't check for 0. When you do, that would misplace the next parameters. =IF(COUNTA(M2:P2),IF(OR......

SpikeManZombie · Accepted Answer · 2020-06-12 10:29:45Z

As @JvdV has noted in comment above, your latest formula in comment above is still checking for a space, not a null (empty) cell. Writing " " will look for a cell that contains a space (someone has hit spacebar). Writing "" will check to see if a cell contains nothing, in other words check to see it is empty.

I also noticed the error in your re-write: You wrapped the logical test within the parenthesis for the COUNTA formula. This is your formula corrected:

=IF(COUNTA(M9:P9)=0,(OR(M9="No",OR(N9="No",OR(O9="No",OR(P9="No"))))),"")

However, each OR statement gives a TRUE or FALSE result and can handle multiple arguments, as indicated by @SolarMike so I think you could probably re-write your formula to as follows to get the same result, if I have understood your requirements correctly:

=IF(COUNTA(M9:P9)=0,IF(COUNTIF(M9:P9,"No")>0,"Yes","No"),"")

Collectives™ on Stack Overflow

Double nested IF statement formula in Excel

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related