Calculating grouped running totals in Google Sheets traditionally meant dragging volatile SUMIF or SUMIFS formulas across thousands of rows, causing browser lag, calculation deadlocks, and silent reference corruptions on row inserts. This masterclass demonstrates how a single, cell-resident SCAN and LAMBDA formula creates an automated, dynamic-spilling cumulative balance that instantly resets whenever your specified criteria change.
The Real-World Business Scenario
Legacy spreadsheet habits cost enterprise teams hundreds of hours during month-end closes. Consider a common operations nightmare at a distribution company, ABC Logistics: the finance team tracks thousands of daily freight ledger transactions across multiple regional warehouses, customer account tiers, and distinct billing cycles.
The ledger requires a real-time cumulative weight or invoice balance. However, that balance must automatically reset back to zero whenever the Region changes or whenever a new Fiscal Month begins.
The junior analyst's default instinct is to enter an expanding range formula in cell D2:
Then they drag it down 25,000 rows. What happens next is predictable:
- Quadratic Calculation Overhead: Every single row forces Google Sheets to calculate an expanding range. Row 2 calculates 1 row, row 10,000 evaluates 10,000 rows, and row 25,000 evaluates 25,000 rows. The total calculation operations scale to N*(N+1)/2—over 312 million evaluations for just 25,000 rows.
- Row Insertion Vulnerabilities: Sorting the sheet or inserting a new transaction in row 584 severs the relative index, resulting in incorrect sums that go unnoticed until audit reconciliation.
- Formula Maintenance Overhead: New incoming form submissions or automated imports from an ERP database fail to calculate unless someone manually drags down the formula.
By replacing the expanding SUMIFS with functional dynamic arrays (specifically SCAN paired with LAMBDA and row indexing), you compute the entire column from a single cell. The computation completes in linear time O(N), consumes a fraction of memory, and never breaks when new rows are added.
The Core Formula Blueprint
Here is the production-ready master formula. Place this formula in row 2 of your calculation column. It handles dynamic data range limits, evaluates two independent reset conditions, and spills down the sheet automatically:
=LET(
regions, TOCOL(A2:A, 1),
months, TOCOL(B2:B, 1),
amounts, TOCOL(C2:C, 1),
n, ROWS(amounts),
row_indices, SEQUENCE(n),
SCAN(0, row_indices, LAMBDA(prev_acc, i,
IF(i = 1,
INDEX(amounts, 1),
IF(
(INDEX(regions, i) = INDEX(regions, i - 1)) *
(INDEX(months, i) = INDEX(months, i - 1)),
prev_acc + INDEX(amounts, i),
INDEX(amounts, i)
)
)
))
)
In Google Sheets, the SCAN function natively iterates over only one array at a time. By passing a SEQUENCE(n) of row indices as the driving array, we can use INDEX() inside the LAMBDA to compare the current row against the prior row across unlimited criteria columns simultaneously.
Step-by-Step Implementation Walkthrough
Let's walk through this configuration step-by-step using a standardized warehouse ledger for ABC Logistics. In this dataset, the running total in Column D must accumulate shipping weights, but it must reset to the current row's weight whenever Warehouse Region (Col A) changes OR whenever the Month (Col B) changes.
Sample Dataset Structure
| Row # | Column A (Region) | Column B (Month) | Column C (Weight KG) | Column D (Calculated Running Total) |
|---|---|---|---|---|
| 2 | North | 2026-01 | 450 | 450 |
| 3 | North | 2026-01 | 300 | 750 |
| 4 | North | 2026-02 | 500 | 500 (Reset: Month Changed) |
| 5 | North | 2026-02 | 250 | 750 |
| 6 | South | 2026-02 | 600 | 600 (Reset: Region Changed) |
| 7 | South | 2026-02 | 400 | 1000 |
Sanitize and Scope the Dynamic Ingestion Ranges
An open reference like A2:A includes thousands of trailing empty cells down to row 50,000. Evaluating trailing blanks forces lambda iterations to run across thousands of meaningless records. We utilize TOCOL(range, 1) inside a LET block to isolate populated cells:
months, TOCOL(B2:B, 1),
amounts, TOCOL(C2:C, 1)
The second argument (1) instructs TOCOL to ignore blanks completely. This locks the internal array boundary to precisely the number of populated records.
Build an Execution Array via SEQUENCE
Next, determine the exact row count of the valid dataset using n, ROWS(amounts), and generate an integer array with row_indices, SEQUENCE(n). If your data contains 6 rows, this produces an internal vector:
This sequence acts as a structural cursor, allowing random coordinate access across all columns using the current index i and prior index i - 1.
Configure the SCAN Accumulator Logic
The SCAN function takes three specific arguments: SCAN(initial_value, array, lambda).
initial_value: Set to0, establishing a numeric starting point.array: Passed asrow_indices.lambda: Defined asLAMBDA(prev_acc, i, ...)whereprev_accstores the accumulated value returned from the preceding row's calculation, andirepresents the current row index.
On the very first record (i = 1), there is no prior row to evaluate. Comparing row 1 to row 0 would yield an out-of-bounds error. Therefore, we explicitly handle the base case:
Execute Multi-Condition Boolean Multiplication
For every index where i > 1, we evaluate whether both conditions remain true compared to the previous row. In spreadsheet array calculations, the standard AND() function cannot be used inside iterative lambdas because it aggregates entire arrays into a single boolean result. Instead, we use Boolean Multiplication:
This evaluates cleanly according to binary logic:
- If Region matches (
TRUE = 1) AND Month matches (TRUE = 1): 1 * 1 = 1 (Evaluates toTRUE). The running total increments:prev_acc + INDEX(amounts, i). - If Region changes (
0) OR Month changes (0): 1 * 0 = 0 or 0 * 1 = 0. The condition fails, resetting the accumulator immediately to the current row's transaction value:INDEX(amounts, i).
Cross-Platform Behavior: Google Sheets vs. Microsoft Excel
While both applications support dynamic arrays, running totals with SCAN and LAMBDA present critical environmental differences:
| Feature / Behavior | Google Sheets | Microsoft Excel (365 / 2024) |
|---|---|---|
| Open-Ended Array Bounds | Supports A2:A syntax directly, but requires TOCOL(..., 1) to avoid iterating thousands of empty rows. |
Does not support open references like A2:A. Requires Excel Tables (Table1[Region]) or dynamic ranges (A2:INDEX(A:A, COUNTA(A:A))). |
| Spill Obstruction Handling | Throws a #REF! error: "Result was not expanded because it would overwrite data in cell D5". |
Throws a dedicated #SPILL! error badge with a visual dashed boundary box around the blocking cells. |
| Execution Engine & Memory | Cloud JavaScript engine (V8). Extremely fast on recursive arrays up to ~50k rows; can throttle on 100k+ rows. | Native multi-threaded C++ engine. Handles deep iterations faster across massive datasets without cloud latency. |
Error Troubleshooting Ledger: Why Grouped Running Totals Break
Symptom: Instead of adding 450 + 300 = 750, the cell outputs 450300 or returns a #VALUE! error.
Root Cause: Column C values were imported as raw text strings (common with CSV bank extracts). In Google Sheets, applying + to text strings can cause implicit type conversion failures or string concatenation.
Symptom: The running total resets when it shouldn't, or fails to reset when visually identical categories appear.
Root Cause: Row 3 contains "North" while row 4 contains "North " (with an invisible trailing space). The equality test "North" = "North " evaluates to FALSE.
Symptom: The formula produces correct numbers for the first 100 rows, then outputs #N/A for all subsequent rows.
Root Cause: TOCOL(range, 1) stripped blank cells unevenly because Column A had 150 non-empty cells while Column C had missing data (142 non-empty cells). As a result, INDEX(amounts, 143) fails out of bounds.
Symptom: The single formula cell in D2 stops working and displays a broad #REF! error badge across the top.
Root Cause: A user typed a manual note or an empty space character somewhere down in Column D (e.g., cell D842), preventing the dynamic array from populating that row.
Production Best Practices & Workbook Optimization
Architecting High-Performance Workbooks
Financial models that grind to a halt usually suffer from unconstrained recalculation trees. Follow these principles to maintain sub-second response times:
-
Eliminate Volatile Functions: Avoid using
OFFSETorINDIRECTto construct dynamic ranges. These functions force Google Sheets to recalculate the entire column whenever any cell in the entire workbook edits.INDEX,TOCOL, andCHOOSEROWSare non-volatile and only recalculate when their referenced inputs change. -
Sort at the Ingestion Layer: Running totals that reset by category require the dataset to be physically or virtually sorted by those category columns. If your raw transaction log is unordered, do not run nested loops inside the lambda. Wrap the input dataset in a single
SORT(A2:C, 1, TRUE, 2, TRUE)at the start of yourLETpipeline. -
Single Cell Governance: Consolidating 25,000 independent formulas into one master formula in cell
D2cuts Google Sheets XML file size by over 70%, drastically accelerating initial workbook load times and mobile sheet rendering.
Advanced Edge Case: Case-Sensitive Resets with Unsorted Source Data
In real-world tracking, category codes may be case-sensitive (for example, batch code "lot-a" vs. "LOT-A" denoting distinct testing phases), or transactions might arrive out of sequence. Standard equality (=) in Google Sheets is case-insensitive.
The advanced formula below addresses both challenges: it applies a virtual sort using SORT, enforces exact case sensitivity via EXACT(), and protects the sheet against blank row inserts:
=LET(
raw_data, FILTER(A2:C, A2:A <> "", C2:C <> ""),
sorted_data, SORT(raw_data, 1, TRUE, 2, TRUE),
regions, CHOOSECOLS(sorted_data, 1),
months, CHOOSECOLS(sorted_data, 2),
amounts, CHOOSECOLS(sorted_data, 3),
n, ROWS(amounts),
SCAN(0, SEQUENCE(n), LAMBDA(acc, i,
IF(i = 1,
INDEX(amounts, 1),
IF(
EXACT(INDEX(regions, i), INDEX(regions, i - 1)) *
(INDEX(months, i) = INDEX(months, i - 1)),
acc + INDEX(amounts, i),
INDEX(amounts, i)
)
)
))
)
When sorting and filtering virtually inside the formula, the output array will match the row count of sorted_data rather than the physical rows of the raw table. For reporting layouts, output the entire transformed table together using HSTACK(sorted_data, calculated_running_totals) to keep transactions and running balances aligned.
Real-World Spreadsheet FAQ
1. Can I use the traditional ARRAYFORMULA function instead of SCAN?
No. ARRAYFORMULA can distribute independent row-by-row operations (such as A2:A * B2:B), but it cannot hold an internal state accumulator across iterations. A running total requires each row's calculation to be aware of the prior row's output. That recursive memory requires helper functions like SCAN or REDUCE.
2. What is the fundamental difference between SCAN and REDUCE?
Both iterate through arrays using a lambda accumulator. However, REDUCE collapses the entire dataset down into a single final summary value (similar to calculating the final balance of an account). SCAN preserves every intermediate state and returns an array of the exact same size as the input, making it specifically suited for running totals and balance ledgers.
3. Will this formula slow down if my dataset expands to 50,000+ rows?
While SCAN is much faster than dragging down 50,000 SUMIFS formulas, passing very large virtual ranges through multiple INDEX calls inside the cloud V8 engine can still produce a slight calculation delay. If your sheet exceeds 75,000 rows, consider sorting the data by group and using a single, vectorized helper column that flags reset points, or summarize historical transactions using a QUERY pivot.
4. Why can't I just use AND(condition1, condition2) inside the LAMBDA?
In both Google Sheets and Excel, the AND() and OR() functions are array-aggregating formulas. They evaluate every argument in the entire array and return a single scalar TRUE or FALSE, rather than an element-by-element evaluation. Using Boolean multiplication ((cond1) * (cond2)) guarantees row-by-row logical evaluation.
5. How do I adapt this formula for three or more reset conditions?
Chain additional boolean equality terms using the multiplication operator:
((INDEX(colA, i) = INDEX(colA, i-1)) * (INDEX(colB, i) = INDEX(colB, i-1)) * (INDEX(colC, i) = INDEX(colC, i-1))).
The accumulator will continue running only when all three conditions return 1; if any column changes, the product evaluates to 0 and triggers a reset.
6. How can I make this formula work in Microsoft Excel without syntax errors?
In Excel 365, avoid open references like A2:A. Instead, convert your data into an Excel Table (e.g., named LedgerTable) and reference columns directly:
regions, LedgerTable[Region], months, LedgerTable[Month], amounts, LedgerTable[Amount].
The rest of the LET, SEQUENCE, and SCAN structure operates identically.
7. What happens if a transaction amount in Column C is negative?
The logic handles credits, debits, and negative adjustments seamlessly. prev_acc + INDEX(amounts, i) respects standard signs. For instance, if the running balance is 750 and the next record contains -200, the accumulator correctly updates to 550.
Comments