Create and sum a variable range more efficiently than OFFSET

[mountainman88]

I have a file that is summing a YTD value by returning a range based on the month. The data for all 12 months exists in the same tab and an OFFSET function linked to the current month number eg 6 for June, sums and returns a range of 6 cells. This is currently performed on every single line of a P+L on many, many tabs.

I know OFFSET is volatile, what alternatives are there to perform the above less processor intensively.

thanks

 

[Eric W]

It depends on your layout. Based on your description, SUMIFS, SUM(INDEX:INDEX), or SUMPRODUCT could all work. If you could show a sanitized example of your sheet, showing the existing formulas, we can give you some options.

 

[mountainman88]

Column L contains the offset I want to replace with something more efficient. Period_No is a single named range containing the month number. In this instance it is 11 so totalling YTD November.

The P&L is many rows, there are many P&Ls and 5 12-month tables on each tab.

NOV.xlsx
	B										L																																																																BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI
3											YTD																																																																Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
4											Act																																																																Act	Act	Act	Act	Act	Act	Act	Act	Act	Act	Act	FC
5
6
7
8	Total										1174.05																																																																104.94	104.97	103.97	106.28	105.59	105.87	108.86	107.97	108.79	109.82	107.00	107.74
9
10	A										83.01																																																																7.14	7.27	7.97	8.15	7.51	7.05	6.42	7.24	7.73	7.85	8.68	7.25
11	B										15.75																																																																1.19	1.22	1.54	1.48	1.47	1.51	1.55	1.63	1.42	1.41	1.34	1.67
12	C										5.19																																																																0.52	0.44	0.50	0.49	0.46	0.47	0.50	0.48	0.47	0.48	0.40	0.40
13	D										54.06																																																																4.62	4.47	5.03	5.11	4.81	4.50	3.88	4.81	5.48	5.41	5.94	6.21
NA (2)

 

[Toadstool]

Hi Mountainman88,

I was already working on an example so this is a slightly different format but it demonstrates SUMPRODUCT, INDIRECT and SUMIFS options. All recalculated in 7 to 8 seconds on 100,000 rows of data with SUMPRODUCT seeming the fastest.

Mountainman88-2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Month
2		6
3			01-Jan-21	01-Feb-21	01-Mar-21	01-Apr-21	01-May-21	01-Jun-21	01-Jul-21	01-Aug-21	01-Sep-21	01-Oct-21	01-Nov-21	01-Dec-21
4	SUMPRODUCT	231	11	22	33	44	55	66	77	88	99	111	222	333
5	INDIRECT	231	11	22	33	44	55	66	77	88	99	111	222	333
6	SUMIFS	231	11	22	33	44	55	66	77	88	99	111	222	333
Sheet1

Cell Formulas
Range	Formula
D3:N3	D3	=EOMONTH(C3,0)+1
B4	B4	=SUMPRODUCT(($C4:$N4*($C$3:$N$3<=(DATE(YEAR($C$3),$B$2,1)))))
B5	B5	=SUM(INDIRECT(ADDRESS(ROW(),COLUMN($C$3))&":"&ADDRESS(ROW(),COLUMN($C$3)+$B$2-1)))
B6	B6	=SUMIFS($C6:$N6,$C$3:$N$3,"<="&EOMONTH($C$3,-1+$B$2))

 

[mountainman88]

Hi Mountainman88,

I was already working on an example so this is a slightly different format but it demonstrates SUMPRODUCT, INDIRECT and SUMIFS options. All recalculated in 7 to 8 seconds on 100,000 rows of data with SUMPRODUCT seeming the fastest.

Mountainman88-2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Month
2		6
3			01-Jan-21	01-Feb-21	01-Mar-21	01-Apr-21	01-May-21	01-Jun-21	01-Jul-21	01-Aug-21	01-Sep-21	01-Oct-21	01-Nov-21	01-Dec-21
4	SUMPRODUCT	231	11	22	33	44	55	66	77	88	99	111	222	333
5	INDIRECT	231	11	22	33	44	55	66	77	88	99	111	222	333
6	SUMIFS	231	11	22	33	44	55	66	77	88	99	111	222	333
Sheet1

Cell Formulas
Range	Formula
D3:N3	D3	=EOMONTH(C3,0)+1
B4	B4	=SUMPRODUCT(($C4:$N4*($C$3:$N$3<=(DATE(YEAR($C$3),$B$2,1)))))
B5	B5	=SUM(INDIRECT(ADDRESS(ROW(),COLUMN($C$3))&":"&ADDRESS(ROW(),COLUMN($C$3)+$B$2-1)))
B6	B6	=SUMIFS($C6:$N6,$C$3:$N$3,"<="&EOMONTH($C$3,-1+$B$2))

Thanks I know all these methods but are any of these proven to be less processor intensive than OFFSET as it’s currently used?

There are 100 lines in each p&l, 5 sets of 12-month tables per p&l across around 85 tabs. I know for sure INDIRECT won’t cut it, I’m guessing the same for SUMPRODUCT and SUMIFS.

 

[Eric W]

You could try:

=SUM($BX8:INDEX($BX8:$CI8,Period_No-1))

INDEX is non-volatile.

 

[mountainman88]

You could try:

=SUM($BX8:INDEX($BX8:$CI8,Period_No-1))

INDEX is non-volatile.

I think this is what I’m looking for. I didn’t know you can just use index like that without an indirect. Thanks!

 

[Eric W]

Yep, that's a nice trick that isn't well-known. Glad we could help!