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

Calculating running totals (cumulative sums) is one of the most common tasks in financial analysis, inventory management, and operational reporting. Historically, Google Sheets users calculated running balances by writing a formula like =SUM($B$2:B2) in the first row and manually dragging it down the entire column. While simple, this traditional approach suffers from serious limitations: inserting new rows frequently breaks cell references, deleting rows produces reference errors, and extending datasets requires tedious manual copying. With modern array formula updates, Google Sheets provides the SCAN function paired with LAMBDA, delivering a single, dynamic array formula that generates running totals automatically across an entire dataset.

What Is the SCAN Function in Google Sheets?

The SCAN function belongs to Google Sheets’ family of functional programming helpers (alongside MAP, REDUCE, and BYROW). Unlike REDUCE, which collapses an entire array into a single aggregated total, SCAN scans through an array and outputs an intermediate value at every step. This makes it the ideal mathematical engine for cumulative balances.

Formula Syntax

SCAN(initial_value, array, lambda)

The components of the formula operate as follows:

• initial_value: The starting benchmark before calculation commences (typically 0 for running totals).
• array: The range of cells or dynamic array containing the numbers to accumulate (e.g. B2:B20).
• lambda: A custom LAMBDA(accumulator, current_value, expression) formula that defines how each new value adds to the cumulative pool.

Basic Running Total Example

Suppose column A lists transaction dates and column B records daily revenue figures from B2:B10. To generate a dynamic running balance in column C, navigate to cell C2 and enter the following single formula:

=SCAN(0, B2:B10, LAMBDA(acc, val, acc + val))

Press Enter. Google Sheets instantly spills the cumulative results down rows 2 through 10. You do not need to drag or copy anything:

Date (A)	Daily Revenue (B)	Running Total (C)
2026-10-01	150	150
2026-10-02	200	350
2026-10-03	100	450
2026-10-04	300	750

Handling Open-Ended Ranges and Blank Cells

In production worksheets, data ranges expand as new sales or entries are logged daily. If you write =SCAN(0, B2:B, LAMBDA(acc, val, acc + val)) over an open-ended range, Google Sheets will continue calculating zeros all the way to the bottom of the worksheet.

To keep the output clean and terminate calculation when data ends, incorporate an IF condition inside your LAMBDA:

=SCAN(0, B2:B, LAMBDA(acc, val, IF(ISBLANK(val), "", acc + val)))

Alternatively, filter the range dynamically using FILTER so SCAN only processes non-empty rows:

=SCAN(0, FILTER(B2:B, B2:B<>""), LAMBDA(acc, val, acc + val))

Calculating Running Balances with Credits and Debits

For bookkeeping and bank reconciliations where transactions include deposits (credits) and withdrawals (debits), you can expand the SCAN logic. Suppose column B lists deposits and column C lists withdrawals:

=LET(
  credits, FILTER(B2:B, A2:A<>""),
  debits, FILTER(C2:C, A2:A<>""),
  net, MAP(credits, debits, LAMBDA(c, d, N(c) - N(d))),
  SCAN(0, net, LAMBDA(acc, val, acc + val))
)

This formula subtracts debits from credits per line item and computes a flawless running balance that updates dynamically when transactions are modified or appended.

Why You Should Transition to SCAN and LAMBDA

• Immune to Row Insertions: Because the formula resides exclusively in the header cell (C2), inserting or reordering rows in the middle of your table never causes #REF! errors.
• Calculation Speed: SCAN evaluates natively within Google Sheets’ cloud execution engine, significantly outperforming hundreds of repetitive SUM($B$2:B) iterations on large datasets.
• Effortless Maintenance: Any structural adjustment to your balance logic requires editing only one cell rather than updating thousands of dragged formulas.