Search form

Calculate a running total of a column of cells in Excel

If you have a column of numbers and you want to calculate a running total of the numbers alongside, you can use the SUM() formula combined with a clever use of absolute and relative references.

Imagine your spreadsheet has values in cells A2 through to B20. You want to put a running total of sales (column B) in column C:

Excel - sales data with a cumulative running total

As you can see, column C shows the cumulative running total of the sales in column B.

To reproduce this you would do the following:

  • In cell C2, enter the following formula:
    • =SUM($B$2:B2)
  • This will put the value of B2 into C2. Remember that $C$2 is an absolute reference. This means that anywhere you copy this formula, it will always refer back to C2.
  • Then, copy this formula and paste it into cell C3. The formula should now look like this:
    • =SUM($A$2:A3)
  • This will put the sum of A2 and A3 into B3.
    • Note that, as promised, $B$2 hasn't changed even though you have copied the cell from one place to another.
    • On the other hand, B2 was a relative reference in the previous cell, so it changed from B2 to B3 when you copied and pasted the formula from C2 to C3.
  • Once you've verified that this works as described, you can then copy the formula into each cell from C2 to C20. The final cell, C20, will contain the total of all the numbers in cells B2:B20.

Note that there are some scenarios where a formula like this is more than you need. Check out this comment to see another way to do it that allows to calculate a running balance based on two columns of numbers.

Finally, a common requirement is to calculate a running total based on data between two dates. Click the link to see now to do this.

Do you have any questions about on this lesson, or a specific problem you need to solve? If so, please leave a comment below.

Our Comment Policy.

We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours.

Add a comment to this lesson

Comments on this lesson

Very cool

Very nicely worded. I like being able to understand what I'm doing, instead of just copy/pasting

Thank you

Fantastic tutorial, I can use excel but had never done a running total, it took me about 30 seconds of reading to understand what to do....far better than microsoft help page!

Nice little trick. We'll

Nice little trick. We'll explained. Saved lot of tome for me. Thanks for posting this useful tip. God bless.

running total amount in excel

The Total Amount is incrementing(single cell:H2) when value is entered in the same cell(G2)..e.g today if you enter in G2 the value of 30 the total amount in H2 is 30;when you enter tomorrow in G2(same cell) the value of 50, the total amount in H2 should be 80;and when you enter again 80 in the same cell(G2)the next day, the total amount should now be 160...hope you could shed a light on this...Thank you very much...

Running Total

Thank you for posting this. It worked perfectly.

find Cumulative total of column with large vol different items

Month numbers Tno Name PF own PF co cum own cum co
Sep-13 1 335 Mr A 13598.00 13057.00
Oct-13 2 335 Mr A 14099.00 13558.00
Nov-13 3 335 Mr A 14099.00 13558.00
Dec-13 4 335 Mr A 14099.00 13558.00
Jan-14 5 335 Mr A 14099.00 13558.00
Feb-14 6 335 Mr A 14099.00 13558.00
Mar-14 7 335 Mr A 14099.00 13558.00
Sep-13 1 338 Mr B 13598.00 13057.00
Oct-13 2 338 Mr B 14099.00 13558.00
Nov-13 3 338 Mr B 14099.00 13558.00
Dec-13 4 338 Mr B 14099.00 13558.00
Jan-14 5 338 Mr B 14099.00 13558.00
Feb-14 6 338 Mr B 14099.00 13558.00
Mar-14 7 338 Mr B 14099.00 13558.00

excel formulas, combinations, etc

thanks. Long, I have been searching for a formula. I could explain the problem too. But, today, I got it. thank you. In future, I may request your help.

Complete Column

That works great. It solved my no reference error for the first line, which i need to apply for the whole column (STRG + D).

Thanks!

I'm so happy to have a resource like this available to me! I finally figured out how to use the pesky $ sign in my formulas. Thank you internet stranger!

Hi I seem to have stumbled

Hi I seem to have stumbled across a problem that I simply can't find the answer to. I want to have a running total against the days of the month (so a full year would be 365 lines). Income and outgoings, and the total. I can make the sum of two cells as above, but I can't seem to figure out how to make that cell, for example, D2 carry onto D3/4/5 and only being adjusted by the income and outgoings (B and C). I'd be hugely appreciative if you have a solution, I'm fairly sure it's an easy one but I simply don't have any experience with Excel.

I think the solution you need is simple

Hi Dan

While you could use the approach shown in this lesson, it sounds like you need a simple formula that looks something like this:

=D2+B3-C3

In this formula, I'm assuming the following:

  • This formula will be entered into D3
  • D2 (the cell above) contains your opening balance. This will need to be manually entered.
  • B3 contains Income.
  • C3 contains Expenses.

Essentially, this formula says "take the balance from the previous row, add the income for today and subtract the expenses for today". It will be more efficient and therefore faster than a formula that tries to calculate a running balance based on income and expenses, especially as your spreadsheet gets bigger.

You'd then copy and paste this formula down column D to the end of your table.

Here's an example. Column E shows the formulas from column D:

Excel worksheet example showing a running balance

Some additional thoughts ....

  • There's no reason not to have multiple rows for a given day.
  • I'd make sure that there is a column with the number of the day or the actual date.
  • Then, you can add multiple rows for each day, and sort them by the day/date column.

This approach is useful for reconciling a bank statement, for example, which can have multiple in/out transactions (or no transactions at all) on a given day

You're right, that formula

You're right, that formula sounds exactly what it is. Feel a bit silly now for not realising. Thanks millionleaves :)

Hi

Hi

Do you have the formula that will work, as that just says there is a circular error. I think it's more like C3:B3 but then I'm stuck. I still can't get it to take the previous running total into consideration.

The formula shown in the image is correct

Hi Dan

The formula in the image that I posted is the correct formula.

If you're getting a circular error, you may be using a different formula, e.g.

=D3+B3-C3

If you put this formula in D3 it will generate a circular error since it is trying to add itself to itself.

The correct formula in D3 is this:

=D2+B3-C3

Note that the formula in D3 refers to the cell above it - D2 - to get the previous balance.

 

Thanks again, I think I've

Thanks again, I think I've found it now, it must be my version of Excel or something, it's this =SUM(D1,B3:C3)

won't calculate zeros

I do a daily total of sales. If there were no sales for the day, and "0.00" is entered into the cell, it puts "0.00 into the subtotal column. I need the total for the last day to populate that days running total. How can I force the system to ignore the zero. I always thought that you just entered a condition like =(IF(C18,1,0))*SUM(C$2:C18) What am I doing wrong. If you could help, I would really appreciate it.
Thank you!

Sorted rows running total

How would you get a correct running total for just the rows that are sorted. Ex I need costs of just travel, but main formula is total costs. Lodging, flights, food etc.

Sorted rows running total

How would you get a correct running total for just the rows that are sorted. Ex I need costs of just travel, but main formula is total costs. Lodging, flights, food etc.