Sum different multiple columns based on Month Selected

victoria2207

New Member
Joined
Mar 9, 2018
Messages
17
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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:

Code:
=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.
 
Upvote 0
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!! :eek:

Vic2207
 
Upvote 0
Thanks Aladin,

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

Current MonthRequirements
ProductJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19Mar-19Mar-183 months6 months9 months
AAA - - 1 - 3 - - - - 3 - - - 3 - 3 3 6
BBB - - - 2 - - 8 - - - - - 5 - - 2 10 10
CCC 1 3 1 - 4 6 2 6 17 5 4 12 5 8 - 10 35 55
DDD - 1 - - 4 4 2 6 14 5 4 12 5 6 - 8 31 51
EEE - - 2 - - - - 1 1 - - 1 1 - - - 2 4
FFF 1 1 2 2 - 1 - 1 10 - - 8 6 6 - 3 14 22
GGG - 1 - - - - - - 7 - - 1 - - - - 7 8
<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> </tbody>

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
 
Upvote 0
try this in B2, copy down and across


Excel 2013/2016
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
13 months6 months9 monthsJan-17Feb-17Mar-17Apr-17May-17Jun-17Jul-17Aug-17Sep-17Oct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19
2Apr-1711310010010001011100111000111
312410010011001010111111110011
424701010111011110000010011100
535601111110110011001100111110
623611111100011110011000011000
236
Cell Formulas
RangeFormula
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)))
 
Upvote 0
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
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTU
1Current3 months6 months9 monthsProductJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19Mar-19
2Mar-18336AAA1333
321010BBB285
4103556CCC131462617541258
583051DDD1442614541256
6023EEE21111
731422FFF11221110866
8078GGG171
SUM 1



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

Excel Workbook
ABCDEFGHIJKLMNOPQRSTU
1Current3 months6 months9 monthsProductJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19Mar-19
2Mar-18336AAA1333
321010BBB285
4103556CCC131462617541258
583051DDD1442614541256
6023EEE21111
731422FFF11221110866
8078GGG171
SUM 2
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top