Sumif Sum Range to be a reference

[stroffso]

Hi All,

I have an indirect formula which works fine however I now need to make the SUM range dynamic as its basically summing a different date each week.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]06/10/16 (this date changes each week)[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]8 (this is where the formula would be)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is the formula I currently have
=SUMIFS(INDIRECT($A12&"!H:H"),INDIRECT($A12&"!$A:$A"),$B12,INDIRECT($A12&"!$B:$B"),$C12,INDIRECT($A12&"!$F:$F"),$G12)

Column H in this case is where all of the october 6th is but next week I want the SUM range to be on column I which is October 13th. Obviously I want this to happen without changing it every week. I have made a list table which has all dates in there so I presume I need to link it to this somehow? My question is how do I do that?

thanks in advance

 

[stroffso]

Dont suppose anyone could provide help with this one?

 

[Tetra201]

... Column H in this case is where all of the october 6th is but next week I want the SUM range to be on column I which is October 13th. Obviously I want this to happen without changing it every week. I have made a list table which has all dates in there so I presume I need to link it to this somehow?..

Here is a different approach. Assuming your weekly changing date is in cell B2, try:

=SUMIFS(OFFSET(INDIRECT($A12&"!H:H"),0,ROUND(($B$2-"6-Oct-2016")/7,0)),INDIRECT($A12&"!$A:$A"),$B12,INDIRECT($A12&"!$B:$B"),$C12,INDIRECT($A12&"!$F:$F"),$G12)

 

[stroffso]

Thanks for that. So does this mean I have to change the formula each week where it says "6-Oct..."?

Here is a different approach. Assuming your weekly changing date is in cell B2, try:

=SUMIFS(OFFSET(INDIRECT($A12&"!H:H"),0,ROUND(($B$2-"6-Oct-2016")/7,0)),INDIRECT($A12&"!$A:$A"),$B12,INDIRECT($A12&"!$B:$B"),$C12,INDIRECT($A12&"!$F:$F"),$G12)