drag formula?

[lepend]

Hi all,
Ive got the following formula and want to drag it but changing G:G in relation to the column

=SUMIFS(INDIRECT("'"&$F$4&"'!G:G"),INDIRECT("'"&$F$4&"'!C:C"),$E28,INDIRECT("'"&$F$4&"'!A:A"),$E$4)

Sorry, I didnt make this formula and really dont understand so well how this works so any explanations need to be for a complete newbie
Any help appreciated...
Thanks
lepend

 

[Jonmo1]

Try this

=SUMIFS(OFFSET(INDIRECT("'"&$F$4&"'!G:G"),0,COLUMNS($A:A)-1),INDIRECT("'"&$F$4&"'!C:C"),$E28,INDIRECT("'"&$F$4&"'!A:A"),$E$4)

 

[lepend]

I took off the $ in $E28 and it drags down now.. but I need to drag horizontally and the G:G does not change according to the column

 

[Jonmo1]

That's the purpose of the offset.
You won't actually 'See' the G in the formula change to H I J etc as you drag it across.
But the offset will correctly increment which column is actually being referenced.

 

[lepend]

OK its just I have quite a lot of columns to do manually and if I add a column in the middle of the sheet(s) then I need to manually change all columns on the right of what i add.. I thought there might be an easy way...
Also, is it a simple change to this formula if I want to restrict the amount of cells in that column to calculate.. its just that whenever I enter any data it takes a long time to calculate.. hope im making sense

 

[Jonmo1]

if I add a column in the middle of the sheet(s) then I need to manually change all columns on the right of what i add

Why would you have to do that?
After inserting a column, you could just re-drag the formula from a column 'to the left' of where you inserted


This is really just the nature of indirect, there's not much you can do about it.

 

[lepend]

Ok no problem.. is there a way to make the calculations quicker... It just that for eg: G:G will be looking into 10s of thousands of cells every time I make a data entry

 

[alansidman]

crossposted: https://www.excelforum.com/excel-formulas-and-functions/1216547-drag-forumula.html

 

[lepend]

Sorry didnt know i was breaking any rules

 

[Jonmo1]

You can just change your range references to specific row #s of your actual used area

instead of G:G, use G2:G1000

I understand the need to account for fluctuating range sizes, my rule of thumb (like for construction work) is 10% more than you need.
You must have some number in your mind that you can say, "Surely my data will never be more than xxx rows"
Use that number + 10%
Even if that number is 100,000, it's better than a million.