Cell addresses in a sumifs formula change when I insert columns inn the raw data in another worksheet

[jeffdolton]

Hi,

Every month I copy raw data into a worksheet and insert two columns; one to capture certain data and the other to ensure that the figures match.

I've put together a table using sumifs in another worksheet that extracts data from the other worksheet. This works fine. However, when I next run the report and insert the columns, the sums ifs cell addresses have changed.

I need to to somehow anchor the reference so that the cell address doesn't change.

Many thanks for your help.

 

[Joe4]

One thing to consider that may solve your issue is to use Named ranges. Then, if you refer to the ranges by their names (instead of their addresses), those shouldn't change as you insert more columns.
See: How to Create and Use Excel Named Ranges

If that does not seem to work for you, then I think we would need more information.
It would be helpful for us to see the structure of your data, where you are inserting formulas, and exactly what those formulas look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jeffdolton]

One thing to consider that may solve your issue is to use Named ranges. Then, if you refer to the ranges by their names (instead of their addresses), those shouldn't change as you insert more columns.
See: How to Create and Use Excel Named Ranges

If that does not seem to work for you, then I think we would need more information.
It would be helpful for us to see the structure of your data, where you are inserting formulas, and exactly what those formulas look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Joe4,

Below is the data table. You will see in row 3 the sumif formulae that I have to copy down each time I run a report, as the cell addresses change when I insert two columns in the source data. The source data is in a tab called 'Receipts'. This is data extracted from an EPOS system and I copy this to column A through to column L. A series of macros spits the string under the heading 'description,'. Another fills the content from a lookup table and finally (and this is the important part) a macro inserts two columns E and F in the source data. It is the insertion of these two columns that throws the sumif formulae out. I've copied a minisheet below but it may help if you have the actual file so you can see what's going on. TheOneDrive link is https://frenshampond-my.sharepoint....NBsTbxt8qujiQBkr50Q_olGxAnIhu-JIGvLw?e=2yQXfe.

PS: I have tried to use the INDEX function to count from the first column but can't get this to work.

Many thanks for your help.

Jeff

test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
3	All Payment Methods		=SUMIF('Receipts Output'!Q:Q,"Boat Hire",'Receipts Output'!S:S)	=SUMIF('Receipts Output'!V:V,"Boat Hire",'Receipts Output'!X:X)	=SUMIF('Receipts Output'!AA:AA,"Boat Hire",'Receipts Output'!AC:AC)	=SUMIF('Receipts Output'!AF:AF,"Kitchen",'Receipts Output'!AH:AH)	=SUMIF('Receipts Output'!AK:AK,"Kitchen",'Receipts Output'!AM:AM)	=SUMIF('Receipts Output'!Ap:Ap,"Kitchen",'Receipts Output'!Ar:Ar)
4	Category	Total	Col 1	Col 2	Col 3	Col 4	Col 5	Col 6	Col 7	Col 8	Col 9	Col 10
5	Boat Hire	£ 35.00	£ 25.00	£ 10.00	£ -	£ -	£ -	£ -
6	Merchandise	£ -
7	Sailability	£ -
8	Open Meeting	£ -
9	Coaching	£ -
10	Course Boat Hire	£ -
11	Kitchen	£ 945.65	£ 588.85	£ 251.90	£ 65.45	£ 29.45	£ 10.00	£ -
12	Free	£ 12.60	£ 7.60	£ 5.00	£ -	£ -	£ -	£ -
13
14	Total	£ 993.25	£ 621.45	£ 266.90	£ 65.45	£ 29.45	£ 10.00	£ -	£ -	£ -	£ -	£ -
Summary Table

Cell Formulas
Range	Formula
C5	C5	=SUMIF('Receipts Output'!Q:Q,"Boat Hire",'Receipts Output'!S:S)
D5	D5	=SUMIF('Receipts Output'!V:V,"Boat Hire",'Receipts Output'!X:X)
E5	E5	=SUMIF('Receipts Output'!AG:AG,"Boat Hire",'Receipts Output'!AI:AI)
F5	F5	=SUMIF('Receipts Output'!AP:AP,"Boat Hire",'Receipts Output'!AR:AR)
G5,F12	G5	=SUMIF('Receipts Output'!BI:BI,"Boat Hire",'Receipts Output'!BK:BK)
H5	H5	=SUMIF('Receipts Output'!BW:BW,"Boat Hire",'Receipts Output'!BY:BY)
B5:B12	B5	=SUM(C5:L5)
C11	C11	=SUMIF('Receipts Output'!Q:Q,"Kitchen",'Receipts Output'!S:S)
D11	D11	=SUMIF('Receipts Output'!V:V,"Kitchen",'Receipts Output'!X:X)
E11	E11	=SUMIF('Receipts Output'!AA:AA,"Kitchen",'Receipts Output'!AC:AC)
F11	F11	=SUMIF('Receipts Output'!AF:AF,"Kitchen",'Receipts Output'!AH:AH)
G11	G11	=SUMIF('Receipts Output'!AK:AK,"Kitchen",'Receipts Output'!AM:AM)
H11	H11	=SUMIF('Receipts Output'!AP:AP,"Kitchen",'Receipts Output'!AR:AR)
C12	C12	=SUMIF('Receipts Output'!Q:Q,"Free",'Receipts Output'!S:S)
D12	D12	=SUMIF('Receipts Output'!V:V,"Free",'Receipts Output'!X:X)
E12	E12	=SUMIF('Receipts Output'!AO:AO,"Free",'Receipts Output'!AQ:AQ)
G12	G12	=SUMIF('Receipts Output'!BM:BM,"Boat Hire",'Receipts Output'!BO:BO)
H12	H12	=SUMIF('Receipts Output'!BW:BW,"Free",'Receipts Output'!BY:BY)
B14:L14	B14	=SUM(B5:B13)

 

[Joe4]

Have you considered my suggestion on trying to use Named Ranges instead of direct column references in your formulas?

 

[jeffdolton]

Hi, I have just tried named ranges and unfortunately this doesn't work.

After I've inserted the two new columns, the 'referred to:' box in Name Manager shows that each named column has moved on two columns so Q becomes S and so forth.

 

[Joe4]

I cannot say that I completely understand your last explanation, but I am unable to open your linked file from my current location.
Perhaps it will make more sense if I can see that. I should be able to take a look at it sometime later today.

 

[jeffdolton]

I cannot say that I completely understand your last explanation, but I am unable to open your linked file from my current location.
Perhaps it will make more sense if I can see that. I should be able to take a look at it sometime later today.

Many thanks and sorry I haven’t been clear. It’s a matter of absolute cells that are in a formula that are not absolute when columns are inserted in the data. If I insert a column in the data, say left of column F, then the sumif formula changes from $F$1 to $G$1 but I need the formula referenced to $F$1 - this is just an example. The linked file will help you to understand my lack of clarity. Thanks, Jeff

 

[Eric W]

See if this works for you:

=SUMIF(INDIRECT("'Receipts Output'!Q:Q"),"Boat Hire",INDIRECT("'Receipts Output'!S:S"))

 

[jeffdolton]

See if this works for you:

=SUMIF(INDIRECT("'Receipts Output'!Q:Q"),"Boat Hire",INDIRECT("'Receipts Output'!S:S"))

Works a treat, thanks very much.

 

[Joe4]

Thanks for the assist, Eric!