Results 1 to 6 of 6

Thread: Formula to sum a number of columns within a range

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Formula to sum a number of columns within a range

    Hello!

    I'm trying to come up with a way to sum a certain number of columns within a range. I have 12 columns (Jan-Dec) with data in them, but I want to sum only from like Jan-Jun without having to update all the formulas all the time, I think I should be able to have a formula with the entire range of columns but only returning the the number of columns I need every month. I think by adding this number in a separate cell like 6 for June or something like that.

    How would I go about creating this formula?

    Thanks in advance!

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formula to sum a number of columns within a range

    Welcome to the forum.

    Try:

    ABCDEFGHIJKLMNO
    1StartEndJanFebMarAprMayJunJulAugSepOctNovDec
    226133122226
    32422322223
    44635352145
    542
    65
    7

    Sheet1



    Worksheet Formulas
    CellFormula
    A4=SUM(INDEX(D2:O2,0,A2):INDEX(D100:O100,0,B2))

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula to sum a number of columns within a range

    Hi, Can you use Pivot and add a filter or slicer for first 6 months
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to sum a number of columns within a range

    This works, I just changed the second Index range to be within the same row since I need a total for each row (each account).

    Thanks a lot for your help!

    This is awesome.

  5. #5
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formula to sum a number of columns within a range

    If it's within the same row, there's a shorter formula you can use involving OFFSET. But I'd recommend sticking with the current formula because it's pretty easy to see how to adapt it for other cases, as you've seen!

    Glad we could help!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  6. #6
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula to sum a number of columns within a range

    Glad could help
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

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
  •