# 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.

Thanks in advance #### 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

### Forum statistics

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.

### Which adblocker are you using?    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