Conditional Sum...

[KanMan]

I have the following array formula:

{=SUM(IF((PM=INDEX(Name,ROW($A1)))*((ISNUMBER(MATCH(Date,MajorHolidays,0)))+(ISNUMBER(MATCH(Day,FridayWeekEnd,0)))+(ISNUMBER(MATCH(Day,WeekEnd,0)))+(ISNUMBER(MATCH(Date,MinorHolidays,0)))+(ISNUMBER(MATCH(Date,FridayWeekEnd,0)))),1))*WeekDayPMhours}

I want the SUM to occur only if the first condition (PM=INDEX...) is true and all the other conditions are not. I have tried various combinations of AND (*), OR(+) and NOT(-) but it just doesn't work.

Any ideas.

Thanks.

 

[Scott Huish]

Change all the ISNUMBER to ISNA

 

[KanMan]

Hotpepper:

Thanks for the reply. Appreciate it very much. It still doesn't work though. I tried the Conditional Sum Wizard and came up with this formula:

{=SUM(IF(PM=INDEX(Name,ROW($A1)),IF(Date<>DATEVALUE("7/4/2011"),IF(Date<>DATEVALUE("9/5/2011"),IF(Day<>"Friday",IF(Day<>"Saturday",IF(Day<>"Sunday",1,0),0),0),0),0),0))}

This works but is cumbersome and I would have to copy it into many cells and would have to redo it each quarter. Sort of defeats the purpose of using Excel if not the entire computer. Instead of Date referencing each date how can I make it refer to a range "MajorHolidays".

Thanks again.

 

[Aladin Akyurek]

Hotpepper:

Thanks for the reply. Appreciate it very much. It still doesn't work though. I tried the Conditional Sum Wizard and came up with this formula:

{=SUM(IF(PM=INDEX(Name,ROW($A1)),IF(Date<>DATEVALUE("7/4/2011"),IF(Date<>DATEVALUE("9/5/2011"),IF(Day<>"Friday",IF(Day<>"Saturday",IF(Day<>"Sunday",1,0),0),0),0),0),0))}

This works but is cumbersome and I would have to copy it into many cells and would have to redo it each quarter. Sort of defeats the purpose of using Excel if not the entire computer. Instead of Date referencing each date how can I make it refer to a range "MajorHolidays".

Thanks again.

Try...

=SUM(
    IF(PM=INDEX(Name,ROWS($A$1:A1)),
    IF(1-ISNUMBER(MATCH(Date,{"7/4/2011","9/5/2011"}+0,0)),
    IF(1-ISNUMBER(MATCH(Day,{"Friday","Saturday","Sunday"},0)),
      1))))

If you are on Excel 2007 or later, try a formula with COUNTIFS instead.