Multi Compare in a Sum/If formula error 1

[planopeets]

The below formula, is producing an #N/A error in the cell, If I remove the "Contractor" from the selection critria, it works fine. It is not, "Contractor" causing the problem, since removing "Not Applicable" will also work fine. It is the fact I have mult values to compare.

=SUM((IF(Type={&quot;Not Applicable&quot;,&quot;Contractor&quot;},IF(Area=&quot;CIS&quot;,IF(Job_Function=&quot;Business Consultant&quot;,IF(Status={&quot;Non-Bill&quot;,&quot;Loaner&quot;,&quot;NB-Commit&quot;},IF(Start_Date<=$A24,IF(End_Date>=$A24,(1*Allocated)))))))))

Thanks in Advance for any support.

 

[bluedragon2]

What will this do for you:

put in OR(Type=&quot;Not Applicable&quot;,Type=&quot;Contractor&quot in place of the Type={&quot;Not Applicable&quot;,&quot;Contractor&quot;}

 

[planopeets]

Using the OR(Type=&quot;Not Applicable&quot;,Type=&quot;Contractor&quot did work. Thanks.

Do you know why the Type={&quot;Not Applicable&quot;,&quot;Contractor&quot;}does not work in the formula, yet the Status={&quot;Non-Bill&quot;,&quot;Loaner&quot;,&quot;NB-Commit&quot;} latter in the formula works fine.

 

[bluedragon2]

Not sure why it works for the second one. I would also change it to:

OR(Status=&quot;Non-Bill&quot;,Status=&quot;Loaner&quot;,Status=&quot;NB-Commit&quot

Others may have a better answer, but I think if you do IF(X={&quot;A&quot;,&quot;B&quot;,&quot;C&quot;} and the value of X is B you will get a result of {FALSE,TRUE,FALSE} and I believe the IF statemnet will only go of the first result, FALSE. If X was A then you would have {TRUE,FALSE,FALSE} and the IF function would process it as TRUE.

I am sure I am most likely wrong in that explanation, but that was my thinking.

Blue

 

[planopeets]

Actually, I'm not getting the error anymore, but it does not appear to be using the condition of (OR(Type=&quot;Not Applicable&quot;,Type=&quot;Contractor&quotto only include these two types.

=SUM((IF(OR(Type=&quot;Not Applicable&quot;,Type=&quot;Contractor&quot,IF(Area=&quot;CIS&quot;,IF(Job_Function=&quot;Business Consultant&quot;,IF(Status={&quot;Non-Bill&quot;,&quot;Loaner&quot;,&quot;NB-Commit&quot;},IF(Start_Date<=$A24,IF(End_Date>=$A24,(1*Allocated)))))))))