How to Calculate Running Totals and Cumulative Sums in Excel Using the SCAN Function

Calculating running balances, progressive tax tiers, or cumulative stock inventory in Microsoft Excel has traditionally required dragging formulas down a column (such as =SUM($C$2:C2)). In large spreadsheets containing tens of thousands of rows, these legacy expanding range references create quadratic computational overhead ($O(n^2)$), causing workbook recalculations to lag significantly.

Microsoft introduced the SCAN LAMBDA helper function to perform cumulative operations across dynamic arrays. Unlike REDUCE, which collapses an array into a single final scalar result, SCAN outputs an intermediate array containing the value after each individual calculation step, allowing you to generate dynamic, instantly recalculating running totals from a single formula cell.

Syntax and Arguments of the SCAN Function

The SCAN function iterates through an array, maintaining an internal accumulator that updates at each element:

=SCAN([initial_value], array, LAMBDA(accumulator, value, calculation))
  • initial_value (optional): The starting seed value for the calculation (typically 0 for running sums or 1 for running products).
  • array: The range or dynamic array of values to be evaluated (for example, C2:C20).
  • LAMBDA(accumulator, value, calculation): A custom function with three parts:
    • accumulator (e.g. a): Stores the running total returned by previous iterations.
    • value (e.g. v): Represents the current element being processed in the array.
    • calculation: The mathematical or string expression combining the accumulator and current value.

Calculating a Dynamic Running Total with SCAN

Consider a transaction register where column C2:C20 contains daily sales figures:

  1. Select cell D2 where your running total column should begin.
  2. Enter the following SCAN formula:
=SCAN(0, C2:C20, LAMBDA(a, v, a + v))

Excel starts with 0, adds the first sales value in C2, returns the sum, carries it forward to add to C3, and spills a complete vertical vector of running totals from D2 to D20 automatically.

Calculating Running Balances with Inflows and Outflows

In bank reconciliation statements where revenue and expenses appear in separate columns:

  1. Suppose deposits reside in B2:B20 and withdrawals in C2:C20.
  2. You can compute net changes and generate a running bank balance starting from an initial deposit of £1,000:
=SCAN(1000, B2:B20 - C2:C20, LAMBDA(bal, net, bal + net))

The formula dynamically computes net cash flow for each row and maintains an ongoing ledger balance.

Generating Cumulative String Joins and Audit Trails

SCAN is not restricted to numeric calculations; it can concatenate text vectors to build step-by-step audit paths:

  1. Suppose cells A2:A6 contain workflow status steps (e.g., Submitted, Reviewed, Approved, Dispatched).
  2. To generate an expanding breadcrumb path at each stage:
=SCAN("", A2:A6, LAMBDA(path, step, IF(path="", step, path & " > " & step)))

The output array spills sequentially:

Submitted
Submitted > Reviewed
Submitted > Reviewed > Approved
Submitted > Reviewed > Approved > Dispatched

Performance and Memory Benefits

Using SCAN provides distinct advantages over legacy formulas:

  • Linear Complexity: Operates in $O(n)$ linear time rather than $O(n^2)$, keeping multi-thousand row workbooks responsive.
  • Dynamic Spill Support: When paired with dynamic ranges (such as A2#), the output vector automatically expands or contracts whenever source datasets resize without requiring manual formula copying.
  • Error Containment: If an input cell contains an error (such as #VALUE!), only that step and subsequent cumulative sums fail, leaving preceding historical steps intact for debugging.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.