📎 This article includes 1 downloadable practice file ↓
“How much have we sold so far this month?” “How many orders has this customer placed up to this row?” Running totals and running counts answer those questions, and they’re one of the first things people try to build in Excel — often with a formula that gets slower and slower as rows are added. Here are the methods that stay correct and fast.
In this article
1. Simple running total: the expanding range
Amounts are in column I from row 2. In J2:
=SUM($I$2:I2)Copy it down. The first reference is locked ($I$2), the second isn’t, so the range grows by one row each time: I2:I2, I2:I3, I2:I4…
=J1+I2 (previous total plus this row), with J1 as a header — just remember to keep it in a column without sorting surprises.2. Running total by category
A separate running total for each region, even when rows are mixed:
=SUMIFS($I$2:I2, $B$2:B2, B2)Same expanding-range idea inside SUMIFS: “add up the amounts above me (and mine) where the region matches my region.”
3. Running count: the nth order for this customer
=COUNTIF($D$2:D2, D2)Gives 1 for a customer’s first order, 2 for their second, and so on. Filter for “1” to get each customer’s first purchase, or combine with text: =D2&"-"&COUNTIF($D$2:D2,D2) builds a unique key like “Raj Traders-3”.
4. Month-to-date total
=SUMIFS($I$2:I2, $A$2:A2, ">=" & EOMONTH(A2, -1) + 1)EOMONTH(A2,-1)+1 is the first day of the row’s month, so the total restarts each month. This assumes rows are sorted by date.
5. Excel Tables: structured references
Inside a Table (Ctrl+T) named tblSales:
=SUM(INDEX([Amount], 1):[@Amount])The range from the first Amount to the current row’s Amount. It fills down automatically as new rows are added.
6. One formula for the whole column: SCAN (Excel 365)
=SCAN(0, I2:I201, LAMBDA(total, x, total + x))SCAN walks down the list keeping a running value and spills every step. One formula, no copying down, and it’s quick.
Which method?
| Need | Use |
|---|---|
| Small sheet, simple total | =SUM($I$2:I2) |
| Separate totals per category | SUMIFS with expanding ranges |
| Nth occurrence / first purchase | COUNTIF with expanding range |
| Restart every month | SUMIFS + EOMONTH |
| Excel 365, big data | SCAN |
Where people go wrong
- Both references locked (
$I$2:$I$2) — every row shows the same number. - Neither reference locked — the range slides down instead of growing.
- Sorting after building — the running total is only meaningful in the order you built it; sort first.
- Filtered views — running totals ignore filters. Use
SUBTOTAL(9, $I$2:I2)for a total of visible rows only.
Practice
The combo practice workbook below has a Sales sheet of 200 orders and a task for every formula on this page. Type your formula in the yellow column; the check turns green when the answer matches. The Answers sheet has working versions.
More combinations: all formula combos · functions used here are explained in the Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.