+ Reply to Thread
Results 1 to 3 of 3

Cumulative sum with dynamic array formula

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Cumulative sum with dynamic array formula

    Hi,

    I have a list of numbers in A2:A8 and have a formula that produces a cumulative sum in B2:B9. The number 1 is written in B2 and the rest of the numbers are calculated with =B2+A2 in B3 copied down.

    What I would like to see is a formula that produces the values in B2:B9 using a dynamic array formula that spills the results in the range starting at B2 and doesn't need to be copied down. Any ideas?

    # Cumulative
    3 1
    4 4
    6 8
    1 14
    13 15
    8 28
    2 36
    38

    Best regards,
    Marbleking

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: Cumulative sum with dynamic array formula

    Please try non-volatile

    =MMULT(N(SEQUENCE(ROWS(A2:A9))>SEQUENCE(,ROWS(A2:A9))),N(+A2:A9))+1

    or volatile OFFSET

    =SUBTOTAL(9,OFFSET(A1,,,SEQUENCE(ROWS(A1:A8))))+1

  3. #3
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    166

    Re: Cumulative sum with dynamic array formula

    Works like a charm! Thank you, Bo_Ry! :-)

    Best regards,
    Marbleking

+ 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. [SOLVED] Dynamic Array Cumulative Sum
    By moglij in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-27-2020, 05:09 AM
  2. Replies: 5
    Last Post: 06-10-2019, 12:12 AM
  3. [SOLVED] Array formula to return all steps of cumulative / running total
    By Stormin' in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-03-2018, 05:18 AM
  4. Dynamic Cumulative in a Range
    By excelnewbie2407 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-10-2017, 11:09 AM
  5. Dynamic cumulative sum formula (?!)
    By WineFerret in forum Excel General
    Replies: 4
    Last Post: 05-13-2016, 07:49 AM
  6. Dynamic Row Lookup Array within Array formula
    By OLLY-7 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-17-2014, 04:55 PM
  7. Replies: 6
    Last Post: 05-23-2008, 01:46 PM

Tags for this Thread

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