Help with Nested If Function

[ardykav]

Hi All,

Currently i have an issue where i want to have 15 if(and functions that have 4 conditions in each but it says im not allowed have that many in a nested if statement.

An example of the current formula i am using is:
=IF(AND(H2="Y",I2="Y",J2="Y",K2="Y"),"All are available",IF(AND(H2="Y",I2="Y",J2="Y",K2="N"),"Jess",IF(AND(H2="Y",I2="Y",J2="N",K2="Y"),"Dave",IF(AND(H2="N",I2="Y",J2="Y",K2="N"),"Derek",IF(AND(H2="Y",I2="Y",J2="Y",K2="N"),"Paul",IF(AND(H2="Y",I2="Y",J2="Y",K2="N"),"Dave and jess",IF(AND(H2="Y",I2="Y",J2="Y",K2="N"),"Dave jess and Paul","No one available")))))))

This is the table i am working from. So basically just needs to let me know who is not free for the party?
[TABLE="width: 564"]
<tbody>[TR]
[TD]Paul[/TD]
[TD]Derek[/TD]
[TD]Dave[/TD]
[TD]jess[/TD]
[TD]Option[/TD]
[TD]Not available[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]15[/TD]
[TD]All Are availble[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]14[/TD]
[TD]Jess[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]13[/TD]
[TD]Dave[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]12[/TD]
[TD]Derek[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]11[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]10[/TD]
[TD]Dave and Jess


All help greatly appreciated,

thanks
[/TD]
[/TR]
</tbody><colgroup><col span="4"><col><col></colgroup>[/TABLE]

 

[MarcelBeug]

Try:

=IF(COUNTIF(H2:K2,"Y")=4,"All are available",SUBSTITUTE(IF(H2="N",H$1&" and ","")&IF(I2="N",I$1&" and ","")&IF(J2="N",J$1&" and ","")&IF(K2="N",K$1&" and ","")," and ","";COUNTIF(H2:K2,"N")))

If not all are available, this adds the name + " and " to he string for each unavailable person; the SUBSTITUTE will get rid of the last " and ".

 

[ardykav]

Great Marcel thank you very much

 

[MarcelBeug]

You're welcome.