Sum different multiple columns based on Month Selected
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Sum different multiple columns based on Month Selected

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Thank you in advance.

    Vic2207

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Posts
    1,484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Stolen from Micron

    Tips for posting problems:
    1) "doesn't work" is of no help. Post error message numbers and text, if known.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    your title should be as descriptive as possible:
    i.e. VBA 2013 ERROR - object or variable not set issue

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    82,303
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sum different multiple columns based on Month Selected

    You could post a scaled-down sample along with the expected results for that sample.
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum different multiple columns based on Month Selected

    Thanks Aladin,

    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.

    Thank you for your response.

    Vic2207

  6. #6
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    2,770
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sum different multiple columns based on Month Selected

    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



    Worksheet Formulas
    CellFormula
    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)))


  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    2,770
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sum different multiple columns based on Month Selected

    you're welcome

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    36,371
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

    Default 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

    ABCDEFGHIJKLMNOPQRSTU
    1Current3 months6 months9 months ProductJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19Mar-19
    2Mar-18336 AAA 1 3 3 3
    3 21010 BBB 2 8 5
    4 103556 CCC131 462617541258
    5 83051 DDD 1 442614541256
    6 023 EEE 2 11 11
    7 31422 FFF1122 1 110 866
    8 078 GGG 1 7 1

    Spreadsheet Formulas
    CellFormula
    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

    ABCDEFGHIJKLMNOPQRSTU
    1Current3 months6 months9 months ProductJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19Mar-19
    2Mar-18336 AAA 1 3 3 3
    3 21010 BBB 2 8 5
    4 103556 CCC131 462617541258
    5 83051 DDD 1 442614541256
    6 023 EEE 2 11 11
    7 31422 FFF1122 1 110 866
    8 078 GGG 1 7 1

    Spreadsheet Formulas
    CellFormula
    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
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    New Member
    Join Date
    Mar 2018
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com