IF AND statement referencing multiple cells

Question

I'm trying to create an IF statement based on answers provided in multiple cells.  For example:

If C2<6 and C4=yes, then Worksheet

If C2<6 and C4=no, then Form

If C2>=6 and C4=yes, then Nothing

If C2>=6 and C4=no, then Memo

Here's one of the arguments I've been trying but I get a #VALUE error.

=IF(AND(C2<6,C4="yes"),"Worksheet"),IF(AND(C2<6,C4="No"),"Form"),IF(AND(C2>=6,C4="yes"),"Nothing"),IF(AND(C2>=6,C4="No"),"Memo")))))))

Any advice or solutions would be greatly appreciated!

Answer 1

Use this if you want the answer blank if either C2 or C4 are blank:

=IF(OR(C2="",C4=""),"",IF(C2<6,IF(C4="yes","Worksheet","Form"),IF(C4="yes","Nothing","Memo")))

Use this if you want the answer blank if both C2 and C4 are blank:

=IF(AND(C2="",C4=""),"",IF(C2<6,IF(C4="yes","Worksheet","Form"),IF(C4="yes","Nothing","Memo")))

Kevin

Answer 2

use below formula.

=IF(AND(C2<6,C4="Yes"),"WorkSheet",IF(AND(C2<6,C4="No"),"Form",IF(AND(C2>=6,C4="Yes"),"Nothing",IF(AND(C2>=6,C4="No"),"Memo"))))

Use this if you want the answer blank if either C2 or C4 are blank.

=IF(AND(C2<6,C4="Yes"),"WorkSheet",IF(AND(C2<6,C4="No"),"Form",IF(AND(C2>=6,C4="Yes"),"Nothing",IF(AND(C2>=6,C4="No"),"Memo",IF(AND(C2="",C4=""),"")))))

Answer 3

This worked!  However, I want the answer cell to stay blank when the C2 and C4 are blank (haven't been answered yet).  I tried to add IF(C2="","") to the beginning (and end) of the argument, but I'm getting an error message.  Any suggestions?  I appreciate your help!

Answer 4

This will give your results:

=IF(C2<6,IF(C4="yes","Worksheet",IF(C4="no","Form","")),IF(C4="yes","Nothing",IF(C4="no","Memo","")))

The formula will also return a blank cell if C4 is neither "yes" or "no" or is blank.

Regards,

Amit Tandon

Answer 5

=IF(C2<6,IF(C4="yes","Worksheet","Form"),IF(C4="yes","Nothing","Memo"))

Kevin