Sum different multiple columns based on Month Selected

[victoria2207]

Good Morning,

I have a spreadsheet with the months of the year starting from column C with forecast values in the hundreds of rows below.

I am looking for a formula that, if I select the current month in a drop down box, it will sum the next 3, 6 and 9 months totals automatically without having to change the formula every time.

So if I select March 18 - in the 3 month forecast column it will sum April - June and the 6 month forecast column will forecast April to Sept and so on.

Thank you in advance.

Vic2207

 

[RCBricker]

I set up a dummy sheet in which my monthly data starts in column C and my last month of data is in column AC. My drop down is in cell D21. Month 3/6/9 monthly sums are in cells E21/F21/G21 respectfully.

In cell E21 place the following formula:

=SUM(INDIRECT(ADDRESS(2,MATCH($D$21,$A$1:$AC$1,0)+3)):INDIRECT(ADDRESS(14,MATCH($D$21,$A$1:$AC$1,0)+3)))

same formula for 6 and 9 just be sure to change the +3 to +6 or +9 as needed.

Change ranges to reflect your actual ranges.

 

[victoria2207]

Hi RCBricker,

Thank you for the reply, it doesn't quite seem to work as I need a result per row - this seems to return the total sum for all rows.

When I try and only sum row 3 (Change the 14 to 3 in the second part of the formula or delete the second part of the formula altogehter), it means I can't pull the formula down to all the other rows.

I hope this makes sense - I know what I'm trying to say!!

Vic2207

 

[Aladin Akyurek]

You could post a scaled-down sample along with the expected results for that sample.

 

[victoria2207]

Thanks Aladin,

Please see below an example of what I am trying to do:

[TABLE="width: 702"]
<colgroup><col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1564;"> <col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1365;" span="2"> <col width="46" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1621;"> <col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1365;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1507;"> <col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1365;" span="5"> <col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1649;"> <col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1365;" span="3"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1564;"> <col width="13" style="width: 10pt; mso-width-source: userset; mso-width-alt: 455;"> <col width="64" style="width: 48pt;"> <col width="19" style="width: 14pt; mso-width-source: userset; mso-width-alt: 682;"> <col width="64" style="width: 48pt;" span="3"> <tbody>[TR]
[TD="width: 44, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 46, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 42, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 46, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 38, bgcolor: transparent"][/TD]
[TD="width: 44, bgcolor: transparent"][/TD]
[TD="width: 13, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"]Current Month[/TD]
[TD="width: 19, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 128, bgcolor: transparent, colspan: 2"]Requirements[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Product[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Jan-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Feb-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Mar-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Apr-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]May-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Jun-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Jul-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Aug-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Sep-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Oct-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Nov-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Dec-18[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Jan-19[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Feb-19[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]Mar-19[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: #E2EFDA"]Mar-18[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: #DDEBF7"]3 months[/TD]
[TD="class: xl71, bgcolor: #DDEBF7"]6 months[/TD]
[TD="class: xl72, bgcolor: #DDEBF7"]9 months[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]AAA[/TD]
[TD="class: xl74, bgcolor: transparent"] - [/TD]
[TD="class: xl74, bgcolor: transparent"] - [/TD]
[TD="class: xl74, bgcolor: transparent"] 1 [/TD]
[TD="class: xl83, bgcolor: #E2EFDA"] - [/TD]
[TD="class: xl83, bgcolor: #E2EFDA"] 3 [/TD]
[TD="class: xl83, bgcolor: #E2EFDA"] - [/TD]
[TD="class: xl74, bgcolor: transparent"] - [/TD]
[TD="class: xl74, bgcolor: transparent"] - [/TD]
[TD="class: xl74, bgcolor: transparent"] - [/TD]
[TD="class: xl74, bgcolor: transparent"] 3 [/TD]
[TD="class: xl74, bgcolor: transparent"] - [/TD]
[TD="class: xl74, bgcolor: transparent"] - [/TD]
[TD="class: xl74, bgcolor: transparent"] - [/TD]
[TD="class: xl74, bgcolor: transparent"] 3 [/TD]
[TD="class: xl74, bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl86, bgcolor: #E2EFDA"] 3 [/TD]
[TD="class: xl76, bgcolor: #DDEBF7"] 3 [/TD]
[TD="class: xl77, bgcolor: #DDEBF7"] 6 [/TD]
[/TR]
[TR]
[TD="class: xl78, bgcolor: transparent"]BBB[/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl84, bgcolor: #FFF2CC"] 2 [/TD]
[TD="class: xl84, bgcolor: #FFF2CC"] - [/TD]
[TD="class: xl84, bgcolor: #FFF2CC"] - [/TD]
[TD="class: xl84, bgcolor: #FFF2CC"] 8 [/TD]
[TD="class: xl84, bgcolor: #FFF2CC"] - [/TD]
[TD="class: xl84, bgcolor: #FFF2CC"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 5 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: #DDEBF7"] 2 [/TD]
[TD="class: xl84, bgcolor: #FFF2CC"] 10 [/TD]
[TD="class: xl82, bgcolor: #DDEBF7"] 10 [/TD]
[/TR]
[TR]
[TD="class: xl78, bgcolor: transparent"]CCC[/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] 3 [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl85, bgcolor: #FCE4D6"] - [/TD]
[TD="class: xl85, bgcolor: #FCE4D6"] 4 [/TD]
[TD="class: xl85, bgcolor: #FCE4D6"] 6 [/TD]
[TD="class: xl85, bgcolor: #FCE4D6"] 2 [/TD]
[TD="class: xl85, bgcolor: #FCE4D6"] 6 [/TD]
[TD="class: xl85, bgcolor: #FCE4D6"] 17 [/TD]
[TD="class: xl85, bgcolor: #FCE4D6"] 5 [/TD]
[TD="class: xl85, bgcolor: #FCE4D6"] 4 [/TD]
[TD="class: xl85, bgcolor: #FCE4D6"] 12 [/TD]
[TD="class: xl79, bgcolor: transparent"] 5 [/TD]
[TD="class: xl79, bgcolor: transparent"] 8 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: #DDEBF7"] 10 [/TD]
[TD="class: xl81, bgcolor: #DDEBF7"] 35 [/TD]
[TD="class: xl87, bgcolor: #FCE4D6"] 55 [/TD]
[/TR]
[TR]
[TD="class: xl78, bgcolor: transparent"]DDD[/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 4 [/TD]
[TD="class: xl79, bgcolor: transparent"] 4 [/TD]
[TD="class: xl79, bgcolor: transparent"] 2 [/TD]
[TD="class: xl79, bgcolor: transparent"] 6 [/TD]
[TD="class: xl79, bgcolor: transparent"] 14 [/TD]
[TD="class: xl79, bgcolor: transparent"] 5 [/TD]
[TD="class: xl79, bgcolor: transparent"] 4 [/TD]
[TD="class: xl79, bgcolor: transparent"] 12 [/TD]
[TD="class: xl79, bgcolor: transparent"] 5 [/TD]
[TD="class: xl79, bgcolor: transparent"] 6 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: #DDEBF7"] 8 [/TD]
[TD="class: xl81, bgcolor: #DDEBF7"] 31 [/TD]
[TD="class: xl82, bgcolor: #DDEBF7"] 51 [/TD]
[/TR]
[TR]
[TD="class: xl78, bgcolor: transparent"]EEE[/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 2 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: #DDEBF7"] - [/TD]
[TD="class: xl81, bgcolor: #DDEBF7"] 2 [/TD]
[TD="class: xl82, bgcolor: #DDEBF7"] 4 [/TD]
[/TR]
[TR]
[TD="class: xl78, bgcolor: transparent"]FFF[/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] 2 [/TD]
[TD="class: xl79, bgcolor: transparent"] 2 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] 10 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 8 [/TD]
[TD="class: xl79, bgcolor: transparent"] 6 [/TD]
[TD="class: xl79, bgcolor: transparent"] 6 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: #DDEBF7"] 3 [/TD]
[TD="class: xl81, bgcolor: #DDEBF7"] 14 [/TD]
[TD="class: xl82, bgcolor: #DDEBF7"] 22 [/TD]
[/TR]
[TR]
[TD="class: xl78, bgcolor: transparent"]GGG[/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 7 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] 1 [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="class: xl79, bgcolor: transparent"] - [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: #DDEBF7"] - [/TD]
[TD="class: xl81, bgcolor: #DDEBF7"] 7 [/TD]
[TD="class: xl82, bgcolor: #DDEBF7"] 8 [/TD]
[/TR]
</tbody>[/TABLE]

Sorry it doesn't look the best but hopefully you can see what I'm trying to do. When the current month changes, each row's 3 month forward looking forecast changes to the subsequent 3 columns and so on.

Thank you for your response.

Vic2207

 

[AlanY]

try this in B2, copy down and across

Excel 2013/2016
	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	AB	AC	AD
1		3 months	6 months	9 months	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19
2	Apr-17	1	1	3	1	0	0	1	0	0	1	0	0	0	1	0	1	1	1	0	0	1	1	1	0	0	0	1	1	1
3		1	2	4	1	0	0	1	0	0	1	1	0	0	1	0	1	0	1	1	1	1	1	1	1	1	0	0	1	1
4		2	4	7	0	1	0	1	0	1	1	1	0	1	1	1	1	0	0	0	0	0	1	0	0	1	1	1	0	0
5		3	5	6	0	1	1	1	1	1	1	0	1	1	0	0	1	1	0	0	1	1	0	0	1	1	1	1	1	0
6		2	3	6	1	1	1	1	1	1	0	0	0	1	1	1	1	0	0	1	1	0	0	0	0	1	1	0	0	0
236

Cell Formulas
Range	Formula
B2	=SUMIFS($E2:$AD2,$E$1:$AD$1,">"&$A$2,$E$1:$AD$1,"<="&DATE(YEAR($A$2),MONTH($A$2)+(COLUMN(B$1)-COLUMN($A$1))*3,DAY($A$2)))

 

[victoria2207]

Thanks AlanY - This seems to work.

Hopefully I can break this down to understand it as I have several different uses I can put this to so it should be very helpful.

Thanks again.

Vic2207

 

[AlanY]

you're welcome

 

[Peter_SSs]

You could also consider this approach. It uses the volatile function OFFSET but you say you have "hundreds" of rows (rather than perhaps tens of thousands) so the impact should not be too great.

As with Alan's suggestion, the formula shown is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Current	3 months	6 months	9 months		Product	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19
2	Mar-18	3	3	6		AAA			1		3					3				3
3		2	10	10		BBB				2			8						5
4		10	35	56		CCC	1	3	1		4	6	2	6	17	5	4	12	5	8
5		8	30	51		DDD		1			4	4	2	6	14	5	4	12	5	6
6		0	2	3		EEE			2					1	1			1	1
7		3	14	22		FFF	1	1	2	2		1		1	10			8	6	6
8		0	7	8		GGG		1							7			1
SUM 1

If you did want to avoid OFFSET, here is another non-volatile option.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Current	3 months	6 months	9 months		Product	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19
2	Mar-18	3	3	6		AAA			1		3					3				3
3		2	10	10		BBB				2			8						5
4		10	35	56		CCC	1	3	1		4	6	2	6	17	5	4	12	5	8
5		8	30	51		DDD		1			4	4	2	6	14	5	4	12	5	6
6		0	2	3		EEE			2					1	1			1	1
7		3	14	22		FFF	1	1	2	2		1		1	10			8	6	6
8		0	7	8		GGG		1							7			1
SUM 2

 

[victoria2207]

Thanks Peter - These are good options and I'm going to try both to see what suit my various projects best. I'm learning so much through this forum! Its brilliant.