SUMIF INDIRECT with dynamic Sum Range

[larsil]

Hi, I am trying to change the Sum Range B:B dynamically, since the Sum Range on each worksheet (REGION) starts from column B to Z. So I am expecting when copying this formula across the columns the Sum Range B:B will change automatically to C:C and D:D etc. Using the initial formula, will not change the Sum Range of B:B. Please advise.

From:
=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!B:B")))

To:
=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!C:C")))

=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!D:D")))

Thanks,

L.A.

 

[jasonb75]

Because of the way that the formula is made up, the column references are text strings, not actual references. Because of this, all references are absolute regardless of the use or omission of the $ symbols.

The easiest way to create relative references with INDIRECT is by using R1C1 notation (if you understand it). To write your formula in R1C1 notation, you need to work out the number of columns between the cell that the formula is in and the column that you want to look at (even though it you're looking at other sheets, do it as if it was the same sheet).

So if your formula for column B is going into B2, then there would be a 0 column difference, if it is going into A2 then there would be a 1 column difference (B:B is 1 column right of A1), if the formula is going into C2 then it will be a -1 column difference (B:B is 1 column left of C1, when going left you need to use negative numbers).

To translate this into a formula, you would need something like.

=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!C[0]:C[0]",0)))

C refers to a column offset, the [number] enclosed in square brackets is the number of columns as described above.
The additional ,0 after the closing double quotes tells the INDIRECT function that you're using R1C1 notation. Once you have set the first column, the rest will adjust as expected when you drag it right. (The formula will not change, but the relative position will).

More info can be found by searching for R1C1.

 

[Toadstool]

Because of the way that the formula is made up, the column references are text strings, not actual references. ...

A better solution than my kludgy one...

Larsil.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	1		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
2
10
11
12	33	40	60	80	100	120	140	160	180	200	220	240
Sheet2

Cell Formulas
Range	Formula
C1:AA1	C1	=B:B
B12:L12	B12	=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!"&RIGHT(FORMULATEXT(C$1),3))))

Named Ranges
Name	Refers To	Cells
REGIONS	=Sheet2!$A$1	B12:L12

 

[jasonb75]

Another way that should work

=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,OFFSET(INDIRECT("'"&REGIONS&"'!B:B"),,COLUMNS($A:A))))

 

[larsil]

Another way that should work

=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,OFFSET(INDIRECT("'"&REGIONS&"'!B:B"),,COLUMNS($A:A))))

Thanks Jason, I think I can follow this formula, I have tried it but it does not work. I guess the sum_range for this formula is INDIRECT("'"&REGIONS&"'!B:B"), but when copying this formula to another column "B:B" will not change to "C:C". So I still manually change this range. Please advise. Thanks. L.A.

 

[larsil]

A better solution than my kludgy one...

Larsil.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	1		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
2
10
11
12	33	40	60	80	100	120	140	160	180	200	220	240
Sheet2

Cell Formulas
Range	Formula
C1:AA1	C1	=B:B
B12:L12	B12	=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,INDIRECT("'"&REGIONS&"'!"&RIGHT(FORMULATEXT(C$1),3))))

Named Ranges
Name	Refers To	Cells
REGIONS	=Sheet2!$A$1	B12:L12

IT WORKS..!!!!!! THANKS.!!!!!!!

 

[jasonb75]

Sorry, it should have actually been

=SUMPRODUCT(SUMIF(INDIRECT("'"&REGIONS&"'!$A:$A"),$A12,OFFSET(INDIRECT("'"&REGIONS&"'!B:B"),,COLUMNS($A:A)-1)))

It will always say B:B in the formula, OFFSET and COLUMNS calculate the position as you drag right by changing from COLUMNS($A:A)-1 to COLUMNS($A:B)-1, COLUMNS($A:C)-1, etc which moves the position of the formula without changing B:B.

 

[larsil]

Thanks Everyone for your contribution, I have got the solution already, advised by Toadstool. I really appreciate it!. many thanks!

 

[jasonb75]

I have got the solution already, advised by Toadstool.

To be fair, Toadstool did say it was a bit kludgy.

If you want an answer, take the first one that works, if you want a solution then you need to persevere with all suggestions provided. Only then will you learn how to use excel properly.

 

[larsil]

Hi Jason, for sure I will try to find out how R1C1 works, as I have never used it before, but thanks for your inputs as well though. I really appreciate it. Best.