# 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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### 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
10
Views
349
Replies
0
Views
105
Replies
3
Views
234
Replies
7
Views
273
Replies
0
Views
817

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,487
Messages
5,831,980
Members
430,100
Latest member
namhnz

### 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.

### Which adblocker are you using?

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

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