Sum If Array doesn't work if inserting new data

Poptarticus

New Member
Joined
Jul 14, 2010
Messages
10
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
try not to limit the range:

=SUMIF('AA Report'!F:F,xxxxxx,'AA Report'!K:K)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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

Which adblocker are you using?

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
Back
Top