+ Reply to Thread
Results 1 to 6 of 6

Calculate cumulative sum based on index value

  1. #1
    Registered User
    Join Date
    07-10-2019
    Location
    UK
    MS-Off Ver
    365
    Posts
    13

    Calculate cumulative sum based on index value

    Hello all,

    Is it possible to calculate the cumulative total of a range using SUM and INDEX formulae?

    Example below:

    snip.PNG

    I'm comfortable with using an INDEX formula to return the value against one line (ie, index 5 would return the value in B6).
    What I need to do is get the formula to calculate the sum of B2:B6 when the index is 5, B2:B7 when the index is 6, etc etc.

    Hope I have explained this properly!

    Thanks in advance.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Calculate cumulative sum based on index value

    =sum($B$2:INDEX($B:$B,MATCH(5,$A:$A,0)))
    you can replace 5 with the cell reference, in which you have index number
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,396

    Re: Calculate cumulative sum based on index value

    Welcome to the forum.

    It is far from clear what the ultimate objective is here.

    Let's start with this:

    =SUMIF($A$2:$A$13,"<="&5,$B$2:$B$13)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Calculate cumulative sum based on index value

    Or can try
    =sum(offset($b$2,,,5,1))

  5. #5
    Registered User
    Join Date
    07-10-2019
    Location
    UK
    MS-Off Ver
    365
    Posts
    13

    Re: Calculate cumulative sum based on index value

    Quote Originally Posted by samba_ravi View Post
    =sum($B$2:INDEX($B:$B,MATCH(5,$A:$A,0)))
    you can replace 5 with the cell reference, in which you have index number
    Went with a variation on this without using the MATCH element which is working great.

    Thanks for your help Samba!

    Jonathan

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,396

    Re: Calculate cumulative sum based on index value

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. need help with excel formula to calculate based on cumulative
    By zr3cool in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2018, 03:36 PM
  2. Replies: 3
    Last Post: 03-20-2018, 02:04 AM
  3. Calculate cumulative total based on given number in cell
    By channguyen in forum Excel General
    Replies: 5
    Last Post: 10-02-2015, 04:44 AM
  4. Calculate Cumulative Sum
    By juanp21 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-21-2015, 02:03 PM
  5. Replies: 6
    Last Post: 08-07-2013, 06:05 PM
  6. Replies: 6
    Last Post: 04-10-2011, 01:34 PM
  7. How to calculate cumulative values
    By Learner in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-11-2006, 02:35 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1