How to Use the SCAN and LAMBDA Functions in Google Sheets to Calculate Running Totals

Calculating running totals or cumulative balances in financial spreadsheets historically required dragging down incremental formulas like =SUM($B$2:B2) across hundreds of rows. While simple, dragged formulas break when new rows are inserted, do not automatically spill when datasets grow, and degrade spreadsheet calculation performance. With the native SCAN and LAMBDA functions, Google Sheets allows you to generate dynamic running totals across entire arrays using a single formula.

Understanding the Syntax of SCAN

The SCAN function scans an array and returns an array containing intermediate accumulated values produced by an underlying LAMBDA calculation:

• =SCAN(initial_value, array, lambda)
• initial_value: The starting value for the accumulator (typically 0 for cumulative addition).
• array: The source range or dynamic array of values to be scanned.
• lambda: A custom LAMBDA function that takes two arguments: an accumulator (current total) and the current array element.

Creating a Dynamic Running Total with SCAN and LAMBDA

Suppose you have a list of daily revenue figures in cells B2:B20. To generate a cumulative revenue column alongside it:

1. Click in cell C2 where the cumulative sequence should begin.
2. Enter the following formula:
   =SCAN(0, B2:B20, LAMBDA(accumulator, value, accumulator + value))
3. Press Enter. Google Sheets automatically spills the running totals down column C for all 19 entries. As you modify daily numbers in column B, all downstream cumulative figures recalculate instantly.

Handling Blank Rows in Dynamic Datasets

When applying SCAN to open-ended ranges like B2:B, trailing empty cells should not repeat the final balance. You can wrap the formula in an IF condition or filter:

• Clean Spill Without Zero Trailing:
  =SCAN(0, FILTER(B2:B, B2:B<>""), LAMBDA(acc, val, acc + val))
• Running Product or Compounding Growth: Change the operator to calculate compounding interest or cumulative multiplier effects:
  =SCAN(1000, B2:B12, LAMBDA(acc, rate, acc * (1 + rate)))

Adopting SCAN and LAMBDA in Google Sheets eliminates fragile helper columns and keeps your financial models modern, responsive, and robust against structural edits.