How to Use the SCAN Function with LAMBDA in Google Sheets for Running Totals

Calculating running totals, cumulative balances, or rolling sums in Google Sheets historically required dragging cumbersome recursive formulas down thousands of rows or relying on resource-intensive ARRAYFORMULA(SUMIF(...)) constructs that cripple spreadsheet calculation speeds. With the introduction of the SCAN function paired with LAMBDA, spreadsheet builders can now compute running totals dynamically across entire columns with a single, highly performant array formula.

Understanding the Syntax of SCAN and LAMBDA

The SCAN function iterates through an array row-by-row, maintains an internal accumulator value, and outputs the intermediate accumulator state at every single step. Its fundamental syntax is structured as follows:

=SCAN(initial_value, array, LAMBDA(accumulator, current_value, formula_expression))

Breaking down each component:

• initial_value: The starting value of the calculation before processing the first row (commonly set to 0 for running sums, or "" for text concatenation).
• array: The range or array of source numbers to be processed.
• LAMBDA(accumulator, current_value, …): A custom function where accumulator tracks the progressive total from previous rows and current_value references the specific item in the active row.
• formula_expression: The mathematical or logical operation executed at each iteration, such as accumulator + current_value.

Creating a Basic Running Total Formula

Suppose you have sales figures listed in column B from cell B2 to B10. To generate an automatic cumulative sum in column C, enter the following single formula into cell C2:

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

Google Sheets will automatically spill the cumulative totals downward from C2 to C10. Because it operates as an array formula, you do not need to drag the formula handle down the column.

Handling Blank Cells in Open-Ended Ranges

In production spreadsheets, data is constantly added to the bottom of the table. If you pass an open-ended range like B2:B into a standard SCAN formula, the calculation will evaluate empty cells and repeat the final cumulative balance infinitely down your spreadsheet. To prevent this, wrap the LAMBDA expression in an IF condition:

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

When the formula reaches an empty row, it outputs an empty string instead of repeating the preceding total, keeping your spreadsheet clean and uncluttered.

Calculating Running Bank Balances (Debits and Credits)

The SCAN function excels at processing financial ledgers where each row represents either a positive deposit or a negative withdrawal. Consider a ledger where deposits appear in column B and withdrawals appear in column C:

=SCAN(1000, SEQUENCE(ROWS(B2:B10)), LAMBDA(acc, row_idx, 
    acc + INDEX(B2:B10, row_idx) - INDEX(C2:C10, row_idx)
))

Here, the ledger begins with an initial starting balance of 1000 and dynamically reconciles net deposits and withdrawals for each ledger row.

Building Cumulative Text Concatenations with SCAN

While commonly applied to numbers, SCAN works seamlessly with strings. For example, to generate breadcrumb trails or cumulative log messages from a list of project milestones in cells A2:A6:

=SCAN("", A2:A6, LAMBDA(acc, val, IF(acc = "", val, acc & " > " & val)))

This yields a progressive narrative structure showing each phase appended to prior history.

Comparing SCAN to REDUCE and Traditional ARRAYFORMULA

Understanding when to deploy SCAN versus related functions ensures maximum spreadsheet efficiency:

• SCAN vs REDUCE: While both iterate using accumulators, REDUCE outputs only a single final scalar number at the conclusion of all rows, whereas SCAN outputs an array revealing every step.
• Performance Benefits: Traditional ARRAYFORMULA(SUMIF(...)) constructs exhibit O(n²) quadratic calculation overhead on large sheets, causing noticeable browser lag. In contrast, SCAN executes in linear O(n) time, computing tens of thousands of rows instantaneously.

Mastering the SCAN function with LAMBDA elevates your Google Sheets modeling, delivering enterprise-grade performance and effortless maintainability.