How to Show Running Results with SCAN in Excel

SCAN works like REDUCE but spills every intermediate result — running totals, balances and streaks in one formula.

You will learn to

• Spill a running total with SCAN
• Count down a budget step by step
• Track a streak that resets

How it works

SCAN(initial_value, array, LAMBDA(accumulator, value, calculation)) visits each item like REDUCE, but instead of returning only the final answer it spills every step. =SCAN(0,B2:B9,LAMBDA(total,x,total+x)) gives a running total in one formula — no $B$2:B2 ranges to fill down.

Because the LAMBDA can contain any logic, SCAN also handles running balances that count down and streak counters that reset to zero: =SCAN(0,B2:B9,LAMBDA(n,x,IF(x>2500,n+1,0))).

=SCAN(0,B2:B9,LAMBDA(total,x,total+x))