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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

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,343
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,453
Messages
5,523,072
Members
409,496
Latest member
SWH

This Week's Hot Topics

Top