# Sum If Array doesn't work if inserting new data

#### Poptarticus

##### New Member
Hi All,
I'm hoping you can help me. I'm using a Sum If array on one worksheet taking data off another worksheet. The problem is, when I insert the next months report it doesn't work.
Formula:
=SUMIF('AA Report'!F2:F626,xxxxxx,'AA Report'!K2:K626) where xxxxxx is an Account number.

The formula should grab all information that matches xxxxxx from F2 on and total it on this worksheet, but that doesn't seem to work. If I insert at F2 it recalculates at F659 where F2 has been moved to. If I insert the new information at F626, it only calculates up to F626.

Any ideas? I've tried F\$2.

Sorry if this sounds confusing.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Andrew Poulsom

##### MrExcel MVP
If you insert a row between rows 2 and 626 the formula will adjust to include the inserted row. What are you doing exactly?

Note, absolute references are relevant only to copy/paste. They don't fix the references in the formula.

#### bobsan42

##### Well-known Member
try not to limit the range:

=SUMIF('AA Report'!F:F,xxxxxx,'AA Report'!K:K)

#### Poptarticus

##### New Member
I came close not limiting the range. That at least calculates my totals, but now to separate by Office....

However, now if I try this formula:
=SUM(IF('AA Report'!F:F=xxxxxx,IF('AA Report'!H:H="yyyyyy",'AA Report'!K:K,0),0))
(xxxxxx is Account, yyyyyy is Office)
I get a #NUM! Error.

Mr. Poulsom, thanks for the suggestion of inserting the data in the middle of the rows, but that didn't seem to work either. Looks like I've created quite the mess.

#### Andrew Poulsom

##### MrExcel MVP

You can't use entire columns in an array formula (pre Excel 2007). In what way doesn't your original formula adjust when inserting rows?

#### Poptarticus

##### New Member
It adjusts to whatever the cells have now become after inserting data.

You know, maybe I just change the final cell to be 10000 or some large number and just keep inserting at the end of the data.

#### Andrew Poulsom

##### MrExcel MVP

With your original formula, if you insert a row between rows 2 and 626 it becomes:

=SUMIF('AA Report'!F2:F627,xxxxxx,'AA Report'!K2:K627)

Isn't that what you want?

#### Poptarticus

##### New Member
Thanks guys. I'll just have to change the end cell to something extremely large.

I have one months data (April), calculate all account totals by office then insert the next months data (May) to add on to the April totals to keep a running total through the year.

I'll just go with this:

=SUM(IF('AA Report'!F2:F10000=531110,IF('AA Report'!H2:H10000="London",'AA Report'!K2:K10000,0),0))

Replies
7
Views
91
Replies
1
Views
70
Replies
0
Views
63
Replies
3
Views
52
Replies
12
Views
173

1,108,931
Messages
5,525,684
Members
409,660
Latest member
1817538628