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:
=if(and(or(a1=100,b1=200)),not(and(a1=100,b1=200)),"Yes","No")
For three or more conditions, this is a VBA question, and my brain hurts too much to try it.

HTH

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

Simplified XOR...

{=NOT(PRODUCT((A1:B1={100,200})+0))}

No need for brain strain or VBA with 3 or more
conditions...

{=NOT(PRODUCT((A1:C1={100,200,300})+0))}
{=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...

{=SUM((A1:B1={100,200})+0)=1}
{=SUM((A1:C1={100,200,300})+0)=1}
{=SUM((A1:D1={100,200,300,400})+0)=1}