# Array SumIf formula

L

#### Legacy 96851

##### Guest
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)
Code:
``````{= 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:
Code:
``````{= 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.

### Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Richard Schollar

##### MrExcel MVP
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

L

#### Legacy 96851

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

Replies
1
Views
487
Replies
4
Views
375
Replies
6
Views
611
Replies
2
Views
274
Replies
1
Views
245

Threads
1,190,781
Messages
5,982,867
Members
439,802
Latest member
Teiho

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back