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

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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
try not to limit the range:

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

Poptarticus

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

ADVERTISEMENT

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
Joined
Jul 14, 2010
Messages
10
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
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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
Joined
Jul 14, 2010
Messages
10
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))
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top