How to Use the REDUCE Function with LAMBDA in Google Sheets to Accumulate Arrays

In spreadsheet modeling, standard aggregation functions like SUM, PRODUCT, and CONCATENATE process arrays uniformly across all values. However, real-world data analysis frequently requires step-by-step state accumulation—such as computing compounding interest balances, generating rolling delimited strings, or performing conditional reductions across dynamic arrays. In Google Sheets, the advanced REDUCE function pairs with LAMBDA to iterate through each item in a collection and fold it into an accumulator variable, producing a single calculated result inside one dynamic formula.

What is the REDUCE Function?

The REDUCE function belongs to Google Sheets’ modern suite of functional programming helper functions (alongside MAP, SCAN, BYROW, and BYCOL). While SCAN returns intermediate values at every step of an iteration, REDUCE returns only the final accumulated value after iterating through the entire input array.

Syntax and Parameters

The formal syntax for REDUCE is structured as follows:

=REDUCE(initial_value, array_or_range, LAMBDA(accumulator, current_value, calculation))

• initial_value: The starting value of the accumulator before iteration begins (e.g., 0 for addition, 1 for multiplication, or "" for text concatenation).
• array_or_range: The array, table range, or formula spill to iterate across.
• LAMBDA: A custom function taking two arguments:
  • accumulator: The running total or intermediate result carried forward from the previous step.
  • current_value: The value of the specific array element currently being processed.
  • calculation: The mathematical or string formula executed on each step to update the accumulator.

Practical Example 1: Custom Delimited Concatenation with Conditions

Suppose you have a list of sales amounts in A2:A8 and you want to concatenate only values exceeding £500 into a formatted summary string separated by hyphens (-):

1. Select the target cell (e.g., C2).
2. Enter the formula:

   =REDUCE("", A2:A8, LAMBDA(acc, val, IF(val > 500, IF(acc = "", TO_TEXT(val), acc & " - " & val), acc)))

3. Press Enter. Google Sheets evaluates each number sequentially:
   • If the number exceeds 500, it appends it to the running accumulator string with a separator.
   • If the number is 500 or less, it preserves the existing accumulator unchanged.
4. The formula outputs a single clean string: 620 - 840 - 1150.

Practical Example 2: Calculating Compounding Balances

When calculating final portfolio growth across variable annual interest rates (e.g., rates listed in cells B2:B6 like 5%, -2%, 8%, 4%, 6%) starting from an initial capital of £10,000:

=REDUCE(10000, B2:B6, LAMBDA(balance, rate, balance * (1 + rate)))

In this workflow:

• balance starts at 10000.
• Step 1: 10000 * 1.05 = 10500.
• Step 2: 10500 * 0.98 = 10290.
• Iteration continues through all rates, returning the final compounding balance without requiring five helper columns.

Practical Example 3: Sequential Text Replacements

A classic challenge in Google Sheets is executing multiple sequential replacements (such as cleaning illegal characters from filenames or converting status codes). By passing an array of character replacements into REDUCE, you can sanitize text in a single pass:

=REDUCE(A2, {"#", "$", "%", "@"}, LAMBDA(text, char, SUBSTITUTE(text, char, "")))

Here, cell A2 is passed as the initial text string, and REDUCE iteratively runs SUBSTITUTE against each symbol in the array constant, outputting a completely stripped, clean string.

Comparison: REDUCE vs SCAN

Feature	REDUCE	SCAN
Output Size	Single value (1 cell)	Array spill (matches input dimensions)
Primary Use Case	Final accumulated total	Running totals / intermediate steps
Memory Footprint	Extremely compact	Requires space for array spill

Mastering the REDUCE function empowers spreadsheet architects to build sophisticated, memory-efficient data transformation pipelines in Google Sheets without resorting to custom Google Apps Script.