IF Statement (Excel 2003)

Topic

New Reply

I have the worst time with IF statements

I need to add an additional IF statement to the formula below that would return a 0 if the date is a holiday. “Holidays” is my named range ($U$3:$U$7). I need to add it to the front of this formula but am not sure how to do this…or at least everything that I have tried is failing. This needs to look at Cell A7 to determine if it falls within the range of holidays.

=IF($B7=2,0,SUM(SUMPRODUCT((FMonth>=EOMONTH($A7,-1)+1)*COAF)-SUMPRODUCT((FMonth>=EOMONTH($A7,0))*COAF))/$S7)

I appreciate your help as always.

Reply | Quote

Replies

Try

=IF(ISERROR(MATCH($A7,Holidays,0)),IF($B7=2,0,SUM(SUMPRODUCT((FMonth>=EOMONTH($A7,-1)+1)*COAF)-SUMPRODUCT((FMonth>=EOMONTH($A7,0))*COAF))/$S7),0)

The formula uses MATCH to look up A7 in the Holidays range. If this results in an error, there is no match i.e. A7 is *not* a holiday, so your formula is used. Otherwise (A7 is a holiday), the formula returns 0.

Reply | Quote

Thanks Hans! Have a fabulous weekend!!

Reply | Quote