How to Use the REDUCE and LAMBDA Functions in Google Sheets to Accumulate Custom Array Values

Standard aggregation functions in Google Sheets—such as SUM, PRODUCT, and TEXTJOIN—perform straightforward scalar combinations over numeric or text ranges. However, when financial analysts or data engineers need to perform multi-stage iterative accumulations, such as compounding interest with variable rates or chaining string transformations, built-in functions often fall short. The REDUCE function, paired with LAMBDA, brings classic functional programming reduction directly into Google Sheets.

What is the REDUCE Function in Google Sheets?

The REDUCE function steps through each item in an array or range sequentially, accumulating values into an ongoing total or state according to a custom calculation you define, ultimately returning a single, consolidated result.

The complete syntax for REDUCE is:

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

Here is how the arguments function:

• initial_value: The starting value of the accumulator before iteration begins (often 0 for addition, 1 for multiplication, or "" for strings).
• array: The range or array of elements to iterate through.
• LAMBDA: The container function that processes each step.
• accumulator: A variable name representing the running result carried forward from the previous iteration.
• current_value: A variable name representing the item currently being evaluated from array.
• formula_expression: The mathematical or logical calculation evaluated on each step, the outcome of which becomes the new accumulator value.

Basic Example: Custom Conditional Summation

Suppose you have sales figures in range B2:B20 and want to sum only positive values while adding a £5 bonus to each qualifying sale. You can achieve this with a single formula:

=REDUCE(0, B2:B20, LAMBDA(acc, val, IF(val > 0, acc + val + 5, acc)))

During evaluation:

1. The formula starts with acc = 0.
2. For each cell in B2:B20, if val is greater than 0, it adds val + 5 to acc.
3. If val is 0 or negative, it carries forward the existing acc unchanged.
4. Once all cells are processed, it outputs the final accumulated total in a single cell.

Chaining Text and String Manipulations

Beyond numbers, REDUCE excels at iterative text manipulation. For example, suppose you have a messy text string in cell A1 and a list of characters or punctuation marks you wish to strip away listed in range E2:E6 (e.g., #, $, %, @, !):

=REDUCE(A1, E2:E6, LAMBDA(text, char, SUBSTITUTE(text, char, "")))

In this scenario:

• The initial_value is the raw string from A1.
• REDUCE takes the first character from E2:E6 and strips it using SUBSTITUTE.
• The resulting cleaned string is passed as the accumulator into the next step, removing the second character.
• After iterating through all characters in E2:E6, the formula returns a completely scrubbed string.

Comparing REDUCE with SCAN and MAP

Function	Accumulation State	Final Output Shape	Primary Use Case
REDUCE	Yes (maintains accumulator)	Single cell (1 × 1)	Folding entire arrays into a single composite metric or string.
SCAN	Yes (maintains accumulator)	Same size as input array (M × N)	Calculating running balances, cumulative sums, or step-by-step histories.
MAP	No (stateless per element)	Same size as input array (M × N)	Transforming each cell or row independently without carrying history.

Advanced Use Case: Dynamic Compound Growth with Variable Rates

Calculating compound interest where each year has a different interest rate is complex with traditional formulas. Suppose you have an initial principal of £10,000 in cell A1 and annual return rates in range C2:C6 (e.g., 5%, 3%, -2%, 7%, 4%):

=REDUCE(A1, C2:C6, LAMBDA(balance, rate, balance * (1 + rate)))

REDUCE compounds the principal year over year, seamlessly accommodating positive and negative fluctuations without helper columns or manual multi-row chaining.

Mastering REDUCE and LAMBDA empowers spreadsheet users to solve complex data accumulation and iterative cleaning problems with elegant, maintainable, single-cell formulas.