Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to create a running total by group in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMIFS(B$2:B2,A$2:A2,A2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Group | Value |
| East | 100 |
| East | 200 |
| East | 150 |
| West | 300 |
| West | 250 |
| West | 100 |
=SUMIFS(B$2:B2,A$2:A2,A2)→100
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Sort your data so rows with the same group label are contiguous: column A = Group, column B = Value.
- 2In C2 enter =SUMIFS(B$2:B2,A$2:A2,A2). The dollar sign on the row number of the start ($2) anchors the range top; the unfixed end row expands as you copy down.
- 3The criteria A2 matches only rows with the same group label up to the current row.
- 4Copy C2 down through C7 — each row accumulates only its own group's values from the top of the range to that row.
- 5When the group label changes (e.g., from 'East' to 'West'), the SUMIFS criteria no longer match previous rows, effectively resetting the running total.
Tips
- Your data must be sorted by group for this expanding-range approach to produce correct group-level running totals.
- To run totals without sorting, use SUMPRODUCT with an array condition: =SUMPRODUCT((A$2:A2=A2)*B$2:B2).
- Add a grand running total alongside it with =SUM(B$2:B2) in column D — compare columns to see how much each group contributes cumulatively.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why does the running total not reset when the group changes?
Check that the group column values are truly identical (no trailing spaces). Use TRIM(A2) or exact match to verify. Also confirm data is sorted by group.
Can I create a running total without sorting my data?
Yes — use =SUMPRODUCT((A$2:A2=A2)*B$2:B2). This evaluates all preceding rows regardless of order but is slower on large datasets.
How do I restart the running total at the start of each month within a group?
Add a third SUMIFS criterion on a date column: =SUMIFS(B$2:B2,A$2:A2,A2,MONTH(C$2:C2),MONTH(C2)) where C holds dates.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.