MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Multiple IF criteria

Posted by Ken May on January 07, 2002 11:41 AM

Is there a way in an IF formula can meet more than 1 criteria or also not match more than 1?

I am not talking about nesting multiple IF formulas, but if a cell equals 100 AND 200, or if a cell equals 100 OR 200 then whatever.

Posted by Mark W. on January 07, 2002 11:48 AM

A cell cannot "equal 100 AND 200". Use the
formula, =IF(OR(A1={100,200}),"Yes","No"), to
test if the cell A1 "equals 100 OR 200".

Posted by Tim Francis-Wright on January 07, 2002 12:43 PM

It sounds like you are looking for an exclusive-or function. For two conditions, this is:
For three or more conditions, this is a VBA question, and my brain hurts too much to try it.


Posted by Mark W. on January 07, 2002 1:11 PM

Simplified XOR...


No need for brain strain or VBA with 3 or more

{=NOT(PRODUCT((A1:D1={100,200,300,400})+0))} ...

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.

Posted by Mark W. on January 07, 2002 1:53 PM

Oops! Found a semantical error...use these instead...