Likes:  0

# Thread: Sum different multiple columns based on Month Selected

1. ## Sum different multiple columns based on Month Selected

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.

Vic2207

2. ## Re: Sum different multiple columns based on Month Selected

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.

3. ## Re: Sum different multiple columns based on Month Selected

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

4. ## Re: Sum different multiple columns based on Month Selected

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

5. ## Re: Sum different multiple columns based on Month Selected

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

 Current Month Requirements 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 Mar-18 3 months 6 months 9 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

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.

Vic2207

6. ## Re: Sum different multiple columns based on Month Selected

try this in B2, copy down and across

Excel 2013/2016
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

Worksheet Formulas
CellFormula

7. ## Re: Sum different multiple columns based on Month Selected

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

8. ## Re: Sum different multiple columns based on Month Selected

you're welcome

9. ## Re: Sum different multiple columns based on Month Selected

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.

SUM 1

 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

 Cell Formula B2 =SUM(OFFSET(\$G2,0,MATCH(\$A\$2,\$G\$1:\$U\$1,0),1,LEFT(B\$1,1)))

Excel tables to the web >> Excel Jeanie HTML 4

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

SUM 2

 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

 Cell Formula B2 =SUM(INDEX(\$G2:\$U2,MATCH(\$A\$2,\$G\$1:\$U\$1,0)+1):INDEX(\$G2:\$U2,MATCH(\$A\$2,\$G\$1:\$U\$1,0)+LEFT(B\$1,1)))

Excel tables to the web >> Excel Jeanie HTML 4

10. ## Re: Sum different multiple columns based on Month Selected

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.