Excel Cumulative Sum - easy way to calculate running total

This short tutorial shows how a usual Excel Sum formula with a clever use of absolute and relative cell references can quickly calculate a running total in your worksheet.

A running total, or cumulative sum, is a sequence of partial sums of a given data set. It is used to show the summation of data as it grows with time (updated every time a new number is added to the sequence).

This technique is very common in everyday use, for example to calculate the current score in games, show year-to-date or month-to-date sales, or compute your bank balance after each withdrawal and deposit. The following examples show the fastest way to calculate running total in Excel and plot a cumulative graph.

How to calculate running total (cumulative sum) in Excel

To calculate a running total in Excel, you can use the SUM function combined with a clever use of absolute and relative cells references.

For example, to calculate the cumulative sum for numbers in column B beginning in cell B2, enter the following formula in C2 and then copy it down to other cells:

=SUM($B$2:B2)

In your running total formula, the first reference should always be an absolute reference with the $ sign ($B$2). Because an absolute reference never changes no matter where the formula moves, it will always refer back to B2. The second reference without the $ sign (B2) is relative and it adjusts based on the relative position of the cell where the formula is copied. For more information about Excel cell references, please see Why use dollar sign ($) in Excel formulas.

So, when our Sum formula is copied to B3, it becomes SUM($B$2:B3), and returns the total of values in cells B2 to B3. In cell B4, the formula turns into SUM($B$2:B4), and totals numbers in cells B2 to B4, and so on:
Excel Cumulative Sum formula

In a similar manner, you can use the Excel SUM function to find the cumulative sum for your bank balance. For this, enter deposits as positive numbers, and withdrawals as negative numbers in some column (column C in this example). And then, to show the running total, enter the following formula in column D:

=SUM($C$2:C2)
Calculating running total for the bank balance

Strictly speaking, the above screenshot shows not exactly a cumulative sum, which implies summation, but some sort of "running total and running difference"  Anyway, who cares about the right word if you've got the desired result, right? :)

At first sight, our Excel Cumulative Sum formula looks perfect, but it does have one significant drawback. When you copy the formula down a column, you will notice that the cumulative totals in the rows below the last cell with a value in column C all show the same number:
The cumulative totals in the rows below the last cell with a value in the dependent column all show the same number.

To fix this, we can improve our running total formula a bit further by embedding it in the IF function:

=IF(C2="","",SUM($C$2:C2))

The formula instructs Excel to do the following: if cell C2 is blank, then return an empty string (blank cell), otherwise apply the cumulative total formula.

Now, you can copy the formula to as many cells as you want, and the formula cells will look empty until you enter a number in the corresponding row in column C. As soon as you do this, the calculated cumulative sum will appear next to each amount:
An improved Excel cumulative sum formula

How to make a cumulative graph in Excel

As soon as you've calculated the running total using the Sum formula, making a cumulative chart in Excel is a matter of minutes.

  1. Select your data, including the Cumulative Sum column, and create a 2-D clustered column chart by clicking the corresponding button on the Insert tab, in the Charts group:
    Create a 2-D clustered column chart.
  2. In the newly created chart, click the Cumulative Sum data series (orange bars in this example), and right click to select Change Series Chart Type... from the context menu.
    Select Change Series Chart Type... from the context menu.
  3. If you are using a recent version of Excel 2013 or Excel 2016, select the Combo chart type, and click on the first icon (Clustered Column - Line) at the top of Change Chart Type dialog:
    In Excel 2013 or Excel 2016, select the Combo chart type.

    Or, you can highlight the Custom Combination icon, and choose the line type you want for the Cumulative Sum data series (Line with Markers in this example):

    Choose the line type you want for the Cumulative Sum data series.

    In Excel 2010 and earlier, simply select the desired line type for the Cumulative Sum series, which you've selected on the previous step:

    In Excel 2010 and earlier, select the desired line type for the Cumulative Sum series.

  4. Click OK, and evaluate your Excel cumulative chart:
    Excel cumulative chart
  5. Optionally, you can right-click the Cumulative Sum line in the chart, and select Add Data Labels from the context menu:
    Add data labels to the Cumulative Sum line.

As the result, your Excel cumulative graph will look similar to this:
Excel cumulative graph with data labels

To embellish your Excel cumulative chart further, you can customize the chart and axes titles, modify the chart legend, choose other chart style and colors, etc. For the detailed instructions, please see our Excel charts tutorial.

This is how you do a running total in Excel. If you are curious to learn a few more useful formulas, check out the below examples. I thank you for reading and hope to see you again soon!

Table of contents

103 comments

  1. I want to set up a spreadsheet so that it totals values per week, i.e. on 12/2/22, I can see the sum of the values from 11/26/22-12/2/22. Then next week, I want it to total the sum of the values from 12/3/22-12/9/22 and so on and so forther.

  2. Hi
    Is there any function that will do running total values of same date and repeat again when new date starts

  3. A B C D E
    1 10 10
    2 22 32
    3 0 22
    4 32 32
    5 16 48
    6 0 16
    7 0 0
    COUNTIF(B1:B7;">0")
    RESULT = 3 from Column B

    Require formula where
    Result = 1
    Which is determined as follows:
    In Column C - Helper column
    IF(B1+B2;"=0";0;B1+B3)
    RESULT is 1

    Formula required to get
    RESULT =1
    Without the Helper Column C

    Thank you.

  4. Thanks for the walk through cheers

  5. How would you calculate the cumulative totals if the dates in column A were not in order? Thanks!

    • have you ever found an answer for this

    • 1 oct 1998 to today how much will be the total cumulative interest is 18per cent per month cumulativly if amount is 10000

  6. how to calculate Successive increase of $2 per 24h or part thereof for every next 24h with formula.
    for example 1st 24 hours charges are $26 for next 24 hours charges will be $28 and so on.

    • Hi!
      The information you provided is not enough to understand your case and give you any advice. How do you time your task?
      Perhaps,

      =24+(INT(A1/24)-1)*2

  7. Hi in office 365 I'm updating my formulas to dynamic formulas, to avoid having to drag along each row/column and to avoid the risk of individual cells being affected by accidental changes (instead of locking the cells).
    Now my challenge is the next:
    I want to have a cumulative dynamic range without having to drag in the traditional way as described in this topic. I would assume this would be possible with a formula like =SUM(A$1:(A1:A10)) where the part after the first : would be the dynamic range --> ideally even in the form of =SUM(A$1:A#). But this does not result in a dynamic output, but only in SUM(A1:A10) c.q. SUM(A1:A#)
    Thus I tried =SUM(A$1:INDIRECT(CELL("address",A#))) However the Cell function is not returning a dynamic range unfortunately.
    My last brainfart was to try to have the output dependent on the position like =IF(A#0,SUM(A$1:A#),)
    This does result in a dynamic output, however each position has the same value (SUM(A1:A#)) and is not a cumulative series.

    Does anyone know of a solution?

  8. Thank you! My question:
    I have a complex table. Therefore, I am not aiming at creating additional column with cumulative sum.
    Can I then, ask Excel to plot a cumulative sum line chart? i.e. the dataseries entry will specify cummulative figures from a (yet to be cumulatively summed) column?
    thanks.

  9. When I follow your example exactly, it doesn't work for me!
    When I have got the formulas in each of the cells, eg in C4 it is =SUM($B$2:B4) it only adds B2and B4, missing out B3.
    For cell B5, the formula is =SUM($B$2:B5) but it only adds B2 and B5, missing out B3 and B4.
    Am I doing something wrong?

  10. This time, I really don't know how to solve it.
    Columns: DateTime [A] | Values [B] | Codes [E]
    So we have an ordered (DateTime) succession of values with several Codes IDs in the rows.
    To try to make my life easier, I did my attempts in two different columns accordingly to plus [N] and minus [O] signs.
    in [N] I need to sum the positive Values of that specific Code until it compares a value of the opposite sign in [B]. When the sign gets back to positive in [B], I need to SUM starting from zero. The same for the Negative column [O].
    So far, with this in [N]
    =IF($B2>0;SUMIFS($B$2:$B2;$E$2:$E2;$E2;$B$2:$B2;">0");"")
    and this in [O]
    =IF($B2<0;SUMIFS($B$2:$B2;$E$2:$E2;$E2;$B$2:$B2;"<0");"")
    I was able to get something, but it doesn't start over.

    But my real goal, is to actually be able to know after how many time periods (i.e. every 15 minutes) the Values for that specific Code go from its last same sign value
    i.e.
    6/06/2020 14:45; +255; apple
    6/07/2020 14:45; -502; apple
    6/08/2020 14:45; +841; apple
    6/09/2020 16:45; -612; apple
    I should find 192 (2days or 48h) for the positive side, and 200 (2days+2h)

    Have a great day

  11. Hi
    ColumnA has for the date value and column B has cumulative value, if we enter the for the date value in column A, automatically the cumulative value will reflect in column B.

    Is there any formula for this kindly guide me

    • Hi,
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred.
      It’ll help me understand it better and find a solution for you.
      But you can automatically insert a value into a blank cell only with a VBA macro

  12. Is there a way to pull a specific date based on the cumulative total. As in, when did the total pass $1700? Is there a function to return that date?

    I am working on a data set where I am projecting X number of real estate developments per year. Lets say at 6 per year. I want to show in what year that the total passes development 14 is built? (This would be year 3). I cannot figure out the function.

    Thanks!

    • Hello!
      Column A contains dates, column B - amounts, column C - cumulative total. The date when the cumulative total exceeded a certain amount can be determined by the formula

      =INDEX(A1:A100,MATCH(1700,C1:C100,1)+1)

      Hope this is what you need.

  13. .Range("L8").Formula = "=IF(AND(F5="",G5="",H5=""),"",I4+F5-G5-H5)" ehy its not work can anyone help

    • Hello!
      Without seeing your data it is difficult to give you any advice. Please describe in more detail what problem you are having with this formula. What does not work?

  14. Thank you for this info - got me going quickly with a spreadsheet I needed in a hurry!
    -- Steve

  15. i am looking to complete the following task.
    row of cells that have a pass or fail drop down option. i have a running total of each but need to work out a way for an aditional cell to feep a running total even when the original cells in the row are cleared and a new option selected. So trac the overall passes and fails as the cells are used multiple times. Is this possible?

  16. Hi Everyone,

    I am looking to calculate a maximum cumulative average of at least 60 cells in length from within a larger range of cells. So, if in a range of data (A1:A101), with the cumulative average running from cell A1 to cell A101, how would I find out the section within this range that has the highest cumulative average? To qualify, it has to be at least 60 cells long.

    Thank you

    • Based on 100 rows of data -
      In column B, work out the average for your range,
      so
      B1 = =Average(B1:B60)
      B2 - =Average(B2:B61)

      Continue this down for 41 rows so all accumulations are covered.

      In column C, use the Rank option to see which set is the highest/lowest group of values.
      = B1 =RANK(B1,$B$1:$B$41,0) all the way down to C40
      This will give you the highest ranked, =RANK(B13,$B$1:$B$41,1) Changing the 0 to a 1 will give you the lowest ranked.

  17. I'm creating a spread sheet to track provisional credit issued and then funds recouped. What I want to be able to do is enter a total into column A say 200.00 then in column B enter the return credit of 50.00 and have that amount auto subtract so that column A now reads 150.00.

    To further complicate it in rare situations I have a third column C where I will on occassion need to subtract from column B without impacting the total in column A. So if I put 25.00 in column C. Column B becomes 25.00 but column A stays 150.00.

    • Hello Kristin!
      If a value is written in cell A1, then the formula can no longer be written to it. This has already been discussed on the blog many times. So your question about the formula in cell A1 does not make sense. Only VBA Macro Can Solve the Problem

  18. Hi,
    I have 2 columns.
    First column is text of various items , with varying amounts of the same items (1-100).
    Second column is just numbers relating to data in first column.
    I am trying to get a running total in the second column for each item in the first column.
    Any help appreciated.

    • Hello Alan!
      If I understand your task correctly, if the name of the item is recorded in column A, the quantity is recorded in column B, then running total can be calculated by the formula

      =SUMIF($A$3:A30,A3,$B$3:B30)

      I hope my advice will help you solve your task.

  19. ABoslutely saved me! Very much useful. Thanks a lot

  20. Hello,

    Can you explain how to run totals in different years? When I do it, it simply starts over for each year. Example;
    Quarters Data per quarter Increasing and Expected Result Actual result
    Q1 2019 10 10 10
    Q2 2019 5 15 15
    Q1 2020 6 21 21
    Q2 2020 3 24 24
    Q1 2021 1 25 1
    Q2 2021 2 27 3

    How can I achieve the increasing and expected result values?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)