Running Totals & More with SCAN

Excel Formulas › Dynamic Arrays

Excel 365LAMBDA

SCAN walks through an array carrying a running result, spilling the value at every step — the cleanest way to make a running total, running max, or cumulative product in a single formula.

Quick formula: to spill a running total of B2:B6:
=SCAN(0, B2:B6, LAMBDA(acc, val, acc + val))
Start at 0; for each value, add it to the accumulator and output the new total.

Functions used (tap for the full reference guide):

SCANLAMBDA

The example

One formula produces the whole running-total column.

AB
1AmountRunning total
2120120
380200
4200400
550450

The formula

The running total, spilled from one cell:

=SCAN(0, A2:A5, LAMBDA(acc, val, acc + val)) // 120, 200, 400, 450

How it works

SCAN is like REDUCE but keeps every intermediate result:

  1. The first argument 0 is the starting accumulator.
  2. It steps through A2:A5; the LAMBDA(acc, val, …) takes the running value acc and the current item val and returns the new running value.
  3. acc + val makes a running sum; SCAN spills the accumulator after each step — the whole running-total column.
  4. Swap the operation for a running max (MAX(acc, val)) or product (acc * val).

SCAN vs REDUCE. Both carry an accumulator through an array. SCAN outputs every step (a running series); REDUCE outputs only the final value (one aggregate).

Try it: interactive demo

Live demo

Pick an operation; see what SCAN spills across {120, 80, 200, 50}.

Spills:

Variations

Running maximum

The highest value seen so far:

=SCAN(-1E308, B2:B100, LAMBDA(a, v, MAX(a, v)))

Cumulative product

Start at 1, multiply:

=SCAN(1, B2:B100, LAMBDA(a, v, a * v))

Running count of matches

Count items meeting a condition as you go:

=SCAN(0, B2:B100, LAMBDA(a, v, a + (v>100)))

Pitfalls & errors

SCAN needs Excel 365. The LAMBDA helper functions don’t exist before 365 — use an expanding-range SUM for a running total in older Excel.

Accumulator and value order. The LAMBDA parameters are (accumulator, value) in that order. Swapping them breaks the logic.

Pick the right starting value. 0 for sums, 1 for products, a very small number for a running max — the wrong seed skews the first result.

Practice workbook

📊
Download the free Running Totals & More with SCAN practice workbook
SCAN running total/max/product examples (results shown), with the expanding-SUM fallback, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make a running total with SCAN in Excel?
Use =SCAN(0, range, LAMBDA(acc, val, acc + val)). It carries a running sum through the array and spills the value at each step. Requires Excel 365.
What's the difference between SCAN and REDUCE?
Both accumulate through an array with a LAMBDA. SCAN outputs every intermediate value (a running series); REDUCE outputs only the final aggregated value.
How do I make a running maximum or product?
Change the LAMBDA: =SCAN(-1E308, range, LAMBDA(a,v, MAX(a,v))) for a running max, or =SCAN(1, range, LAMBDA(a,v, a*v)) for a cumulative product.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Running total · Aggregate with REDUCE · Transform values with MAP

Function references: SCAN · LAMBDA