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?

Paul	Derek	Dave	jess	Option	Not available
Y	Y	Y	Y	15	All Are availble
Y	Y	Y	N	14	Jess
Y	Y	N	Y	13	Dave
Y	N	Y	Y	12	Derek
N	Y	Y	Y	11	Paul
Y	Y	N	N	10	Dave and Jess All help greatly appreciated, thanks

<tbody>
</tbody><colgroup><col span="4"><col><col></colgroup>

 

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