Multiple IF statement

razormb

New Member
Hi there,

I have a spreadsheet with 6 columns of data. If any of say A2, B2, C2, D2 or E2 equal y, then the 6th column will be n. However if A2 and B2 and C2 and D2 and E2 ALL equal n then the 6th column will be Y.

I have got the IF statement working for the first section:

=IF(A2="y","N",IF(B2="y","N",IF(C2="y","N",IF(D2="y","N",IF(E2="y","N","")))))

How do I incorporate the second part into the formula?

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

oldbrewer

Well-known Member
I would concatenate a2 b2 c2 d2 e2 there are only 32 permutations and use a lookup table to return the desired answer

razormb

New Member
Many thanks for replying. What would the formula be? I haven't used concatenate before

oldbrewer

Well-known Member

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

RonB1111

Well-known Member
Try:
Code:
``=IF(AND(A2="y",B2="y",C2="y",D2="y",E2="y"),"n",IF(AND(A2="n",B2="n",C2="n",D2="n",E2="n"),"y",""))``

razormb

New Member
Thanks Ronb and oldbrewer.

Ronb - Yours works if A2 thru to E2 all have data in the cells. However, it could be that just say A2 has an "n" in it. Your formula just doesn't show anything.

RonB1111

Well-known Member
This will ignore blanks,
Code:
``=IF(AND(OR(A2="y",A2=""),OR(B2="y",B2=""),OR(C2="y",C2=""),OR(D2="y",D2=""),OR(E2="y",E2="")),"n",IF(AND(OR(A2="n",A2=""),OR(B2="n",B2=""),OR(C2="n",C2=""),OR(D2="n",D2=""),OR(E2="n",E2="")),"y",""))``

ukmikeb

Well-known Member
Hi

Try :-
Code:
``=IF(OR(A2:E2="n"),"Y",IF(SUM(--(A2:E2="Y"))=5,"n",""))``

entered with Control-Shift-Enter

hth

RonB1111

Well-known Member
This will ignore blanks,
Code:
``=IF(AND(OR(A2="y",A2=""),OR(B2="y",B2=""),OR(C2="y",C2=""),OR(D2="y",D2=""),OR(E2="y",E2="")),"n",IF(AND(OR(A2="n",A2=""),OR(B2="n",B2=""),OR(C2="n",C2=""),OR(D2="n",D2=""),OR(E2="n",E2="")),"y",""))``

This shortened formula will also ignore blanks:
Code:
``=IF(AND(COUNTIF(A2:E2,"n")>0,COUNTIF(A2:E2,"y")=0),"y",IF(AND(COUNTIF(A2:E2,"y")>0,COUNTIF(A2:E2,"n")=0),"n",""))``

razormb

New Member
Many, many thanks for your help. It is much appreciated

Replies
5
Views
169
Replies
3
Views
100
Replies
3
Views
73
Replies
12
Views
345
Replies
26
Views
794

1,191,516
Messages
5,987,017
Members
440,074
Latest member
Emmanuelian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back