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

In financial modelling, operational logistics, and inventory analytics, calculating progressive running metrics—such as cumulative bank balances, year-to-date sales totals, and running stock levels—is an everyday necessity. Traditionally, spreadsheet users constructed running totals by entering an expanding formula like =SUM($B$2:B2) and manually dragging it down hundreds or thousands of rows. However, this legacy method is brittle: inserting or deleting rows corrupts range references, and calculating expanding sums causes exponential recalculation slowdowns on large sheets. The SCAN function in Google Sheets, paired with a custom LAMBDA expression, completely transforms cumulative arithmetic into a single-cell dynamic array formula that spills running values automatically.

Syntax and Architecture of the SCAN Function

The SCAN function shares identical parameter architecture with the REDUCE function, but produces a radically different output structure:

SCAN(initial_value, array_or_range, LAMBDA(accumulator, current_value, formula_expression))

• initial_value: The starting value of the accumulator before traversal begins (commonly 0 for numerical running totals, or an opening balance reference).
• array_or_range: The range or dynamic array of values over which SCAN iterates sequentially.
• LAMBDA(accumulator, current_value, …): A custom anonymous function taking two mandatory arguments:
  • accumulator: The running accumulated state calculated from the immediately preceding step.
  • current_value: The value of the specific element currently being evaluated.
  • formula_expression: The mathematical or logical expression that calculates the updated state.

Crucial Architectural Distinction: While REDUCE collapses the array into a single final value, SCAN outputs an expanded spilled array matching the exact dimensions of the input range, displaying the intermediate accumulator state at every single step.

Creating an Instant Dynamic Running Total

Suppose column B2:B20 contains daily revenue transactions. To generate a real-time running total column that updates automatically without dragging formulas:

=SCAN(0, B2:B20, LAMBDA(running_total, daily_revenue, running_total + daily_revenue))

When placed in cell C2, Google Sheets calculates the running total and spills the results downward through C2:C20. If you insert a new row between existing entries, SCAN recalculates the progressive sum seamlessly without formula reference errors.

Handling Blank Rows Cleanly

When working with dynamic ranges or open-ended columns (e.g., B2:B), unprocessed empty cells cause standard running sums to repeat the last accumulated total indefinitely. You can prevent this with an inline IF condition inside the LAMBDA:

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

If an empty cell is encountered, the formula returns blank, ensuring the output array remains visually clean and professional.

Tracking Inventory Stock Depletion and Running Balances

Cumulative logic often requires combining starting balances with additions and deductions. Suppose an initial warehouse inventory of 500 units is stored in cell D1, and column B2:B15 records incoming shipments (positive values) and outgoing customer orders (negative values):

=SCAN(D1, B2:B15, LAMBDA(stock_level, change, stock_level + change))

Starting at 500 units, SCAN tracks real-time inventory levels after every individual transaction, providing an instant audit trail that prevents accidental stockouts.

Generating Progressive String Ladders and Audit Trails

Because SCAN handles text strings just as effectively as numbers, it can generate progressive audit logs or breadcrumbs:

=SCAN("", A2:A6, LAMBDA(path, step, 
  IF(path = "", step, path & " > " & step)
))

If cells A2:A4 contain “Home”, “Products”, and “Hardware”, SCAN spills progressive breadcrumbs down the column: row 1 displays “Home”, row 2 displays “Home > Products”, and row 3 outputs “Home > Products > Hardware”, illustrating the versatility of stateful vector calculations.