Excel Running Total Formula: How to Keep a Running Total

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for a running total is =SUM($B$2:B2) entered in cell C2 and copied down, assuming your values are in B2:B10. This locks the first cell reference while letting the second one move, so each row sums everything above it. For a running total that resets when a column value changes, use =IF(A2=A1,C1+B2,B2).

How the Standard Running Total Formula Works

The key to a running total is the mixed reference. In =SUM($B$2:B2), the dollar sign before the row and column locks the start of the range. When you copy the formula down, the second reference updates to B3, B4, and so on. So in row 3 the formula becomes =SUM($B$2:B3), which adds B2 and B3. In row 4 it becomes =SUM($B$2:B4), adding B2, B3, and B4. This creates a cumulative sum.

Example: with amounts 10, 20, 30 in B2:B4, the formulas in C2, C3, C4 return 10, 30, and 60. To enter it, type the formula in C2, then double-click the fill handle (the small square at the bottom-right of the cell) or drag it down to the last row of your data.

Using SUM vs. Simple Addition

You might be tempted to write =C2+B3 in cell C3, referring to the previous total. That works, but it has a drawback: if you insert a row in the middle, the formula chain can break or produce wrong results. The SUM approach with a growing range is more robust because it recalculates correctly even if rows are added or deleted above the current row. Also, the SUM version is easier to read when you audit the workbook later. For most running totals, use =SUM($B$2:B2) rather than adding the previous total.

Running Total by Category or Group

If you want a running total that restarts each time a category changes (for example, sales per region), use an IF formula. Assume column A has region names and column B has sales. In C2 enter =IF(A2=A1,C1+B2,B2). This checks if the current region matches the one above. If yes, it adds the current value to the previous running total. If no, it starts a new total with the current value. Copy it down. This works because the formula compares each row to the one directly above it.

Running Total with a Table or Structured References

If your data is in an Excel Table (Insert > Table), you can use structured references. With a table named Table1, columns named Amount and RunningTotal, enter in the first data cell of the RunningTotal column: =SUM(Table1[Amount][@[Amount]:[Amount]]). This is trickier because the syntax for a running total in a table is not as straightforward. A simpler method: in the first data row of the RunningTotal column, enter =SUM($B$2:B2) (adjusting for the actual first data row), then fill down. Excel will keep the structured references if you type the formula normally. For most users, the standard range formula is easier and less error-prone than structured references for running totals.

Common Problems and Fixes

  • Blank cells in the value column: The SUM formula treats blanks as zero, so the total stays correct. If you want to skip blanks and only count non-empty cells, use =IF(B2="","",SUM($B$2:B2)).
  • Text or errors in the value column: SUM ignores text but not error values. If a cell contains #N/A, the running total will show #N/A. Use =IFERROR(SUM($B$2:B2),"") to hide errors.
  • Dragging the formula too far: If you drag beyond your data, you’ll get zeros or repeated totals. Only drag to the last row of your data.
  • Inserting a row in the middle: The SUM formula with a locked start handles this correctly. The simple addition method (=C2+B3) may break if the new row is inserted above the formula.

Alternatives: PivotTable and Quick Analysis

For a one-off running total without formulas, you can use a PivotTable. Create a PivotTable from your data, put the date or category in Rows, and the value in Values. Then add a second value field and change its Show Values As to Running Total (Base Field: the row field). This is useful for reports but doesn’t update automatically when you add new rows unless you refresh. Another option is the Quick Analysis tool: select your data, click the Quick Analysis button (bottom-right of the selection), choose Totals, then select the running total option (a blue downward arrow with a sum sign). This inserts the SUM formula for you.

Frequently Asked Questions

How do I keep a running total in Google Sheets? The same formula works: =SUM($B$2:B2). Google Sheets uses the same syntax. The main difference is the fill handle behavior is identical. For a running total that resets by category, use the same IF formula.

Can I use a running total with filtered data? The SUM formula ignores hidden rows only if you use SUBTOTAL. For a running total that only counts visible rows after filtering, use =SUBTOTAL(9,$B$2:B2) in C2 and copy down. This will recalculate based on visible cells only, but note that the running total will skip hidden rows, which may or may not be what you want.

Why does my running total show 0 or repeat the same number? This usually happens when the first cell reference isn’t locked. If you type =SUM(B2:B2) and copy down, it becomes =SUM(B3:B3), which only shows the current row. Make sure you use $B$2 (with dollar signs) for the start of the range.

What if I want a running total that stops at a certain date or condition? Use SUMIFS with a growing range. For example, to sum amounts up to the date in A2, use =SUMIFS($B$2:B2,$A$2:A2,"<="&A2). This works because the range grows as you copy down, and the condition checks each row's date against the current row's date.

Is there a way to do a running total without a formula? Yes, use a PivotTable with Show Values As > Running Total, or use the Quick Analysis tool. However, these are less flexible for dynamic data that changes frequently. The formula method is the most reliable and easy to edit.

Mac and Excel for the Web Differences

The formulas work identically on Excel for Mac and Excel for the web. The only difference is the keyboard shortcut for copying down: on Windows, press Ctrl+D to fill down; on Mac, press Command+D. The fill handle drag works the same on all platforms. If you're using Excel for the web, the formula bar and autofill are the same, but you might need to click the fill handle and drag rather than double-click if the adjacent column has blanks.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.