Array SumIf formula

[Legacy 96851]

I have the following array formula, which, while cumbersome, correctly performs the intended evaluation. (don't know if formula's get code tags but whatever)

{= SUMIF(ProjectData!$D$2:$D$117, Summary!$B4, ProjectData!$AC$2:$AC$117) 
- SUM(IF(ProjectData!$D$2:$D$117=Summary!$B4, IF(D$2 > ProjectData!$AB$2:$AB$117, ProjectData!$AC$2:$AC$117))) 
- SUM(IF(ProjectData!$D$2:$D$117=Summary!$B4, IF(D$2 < ProjectData!$AA$2:$AA$117, ProjectData!$AC$2:$AC$117)))}

D2 and columns ProjectData!AA and ProjectData!AB are dates, while B4 and ProjectData!D are names. Basically what the formula does is: Sum the value in p.data AC for all rows where the name in B4 is the same as the one in p.data column D and the D2 is in between p.data AA and p.data AB.

However, rows are often being added to ProjectData, so I want to drop the numerical row references for this sheet, making the formula this:

{= SUMIF(ProjectData!$D:$D, Summary!$B4, ProjectData!$AC:$AC) 
- SUM(IF(ProjectData!$D:$D=Summary!$B4, IF(D$2 > ProjectData!$AB:$AB, ProjectData!$AC:$AC))) 
- SUM(IF(ProjectData!$D:$D=Summary!$B4, IF(D$2 < ProjectData!$AA:$AA, ProjectData!$AC:$AC)))}

This gives #NUM! in the cell.

I don't know a ton about how array formulas work, but could anyone explain to me what's wrong with this, and how I could fix it? Thanks for the help.

 

[Richard Schollar]

Hi

Array formulas don't work with whole column refs (in versions of xl prior to 2007, anyhow). You should look into Dynamic Name Ranges so that you can use these in your formulas rather than explicit ranges - Debra Dalgleish has a useful intro to these at:

http://www.contextures.com/xlNames01.html

 

[Legacy 96851]

Thanks a lot. I figured it was something simple like that.