HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

Ellie456

New Member
Joined
Sep 20, 2015
Messages
27
Hi,

I have formulas in worksheet 1 that pulls it's values in from worksheet 2. When instering a column into worksheet 2 my formulas change due to the insertion of the additional column so absolute referencing hasn't helped in my situation as I want to keep the formulas AS IS due to new data being added each day.

My formula is as follows:

SUMIF('worksheet2'!C3:L3,worksheet1'!C10,'worksheet2'!C18:L18)/2

Can someone please show me how I would add INDIRECT to this formula. I have lots of other formulas but if someone can help with this formula I am thinking I should be able to update the rest. I've tried to find a solution but haven't had any luck, mainly because I am not understanding the INDIRECT function but it seems it's the only solution to my problem due to the insertion of new columns in my dataset

Any help would be greatly appreciated.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
1) If you insert a column in A,B or C then the formula will increment to D3:M3 (which makes sense)
2) If you insert a column between D & L then the formula will increment to C3:M3
3) If you insert a column after L then no change will be made to the formula.

What would you want the formula to look at if 1) and 2) occurred?
 
Upvote 0
Hi, here is an option that is impervious to column insertions.

Code:
=SUMIF(INDEX('worksheet2'!3:3,0,3):INDEX('worksheet2'!3:3,0,12),'worksheet1'!C10,INDEX('worksheet2'!18:18,0,3):INDEX('worksheet2'!18:18,0,12))/2
 
Upvote 0
Hi,

My workbook has some VBA, the VBA inserts a new column with the new daily values into column C each day hence the range shift within my current formula
My VBA also then deletes a column towards the end of the table which is column TT which doesn't show in this formula but does in others.

In answer to your question:

I will always need the formula to reference
C3:L3.
In C3:L3 I have the day's of the week, so if C10 equals let's say Friday then I want all Friday values from C18:L18 to be picked up
 
Upvote 0
Hi, here is an option that is impervious to column insertions.

Code:
=SUMIF(INDEX('worksheet2'!3:3,0,3):INDEX('worksheet2'!3:3,0,12),'worksheet1'!C10,INDEX('worksheet2'!18:18,0,3):INDEX('worksheet2'!18:18,0,12))/2



Amazing, this works perfectly thank you so much!!

How does it work, exactly? I'd like to try and change the rest of the formulas that also reference worksheet 2 but are slightly different to the current SUMIF, any additional help you can give is much appreciated
 
Upvote 0
Have worked out the 0,3 and 0,12 which was what I wasn't sure about and once I understood that I could see how the rest of the formula was working.

Thanks again for your help! I spent almost all day trying to find way's to fix this insert column issue and you sorted this for me pretty much instantly.
 
Upvote 0
Hi, here is an option that is impervious to column insertions.

Code:
=SUMIF(INDEX('worksheet2'!3:3,0,3):INDEX('worksheet2'!3:3,0,12),'worksheet1'!C10,INDEX('worksheet2'!18:18,0,3):INDEX('worksheet2'!18:18,0,12))/2


Can you tell me how I would be able to lock the below formula as well in my workbook so the insert column doesn't change the formula?

='worksheet2'!F17
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

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