How do I use REDUCE to concatenate text from a column?

Pass an empty string as the initial value, feed the target column as the array, and write a LAMBDA that adds the current text to the accumulator, inserting a delimiter only when the accumulator isn’t empty.

Can REDUCE return an array instead of a single value?

Yes, the accumulator can be an array, but the LAMBDA must return an array of the same dimensions on each iteration. This is useful for building up a matrix row-by-row, though SCAN is often a clearer choice for that pattern.

Why does REDUCE give a #VALUE! error when my formula works in Google Sheets but not in Excel?

Excel requires the LAMBDA to have exactly two parameters; Google Sheets is more forgiving and will ignore extra parameters. Ensure your LAMBDA signature matches (accumulator, current) to be portable.

Is REDUCE volatile like SUMIF or does it recalculate only when its inputs change?

REDUCE is non-volatile; it recalculates only when the initial value, array, or LAMBDA definition changes, making it efficient for large datasets.

REDUCE function

REDUCE iteratively applies a LAMBDA to each element of an array, carrying forward an accumulator that starts with the initial value.

=REDUCE(initial_value, array, lambda)

Generate a REDUCE formula

Describe what you need. The generator will reach for REDUCE where REDUCE is the right tool, and tell you when it is not.

Arguments

How REDUCE reads its arguments

Argument	Required	Description
initial_value	Required	Any scalar or array that serves as the accumulator's starting point; if the array argument is empty, REDUCE simply returns this value.
array	Required	A range or array that supplies the sequence of values REDUCE will feed to the LAMBDA; a single-column or single-row array works, and empty arrays trigger the edge case described above.
lambda	Required	A LAMBDA with exactly two parameters (accumulator, current) that returns the updated accumulator; mismatched parameter counts cause a #VALUE! error.

Returns

Returns a single value whose type matches the accumulator produced by the LAMBDA.

Availability

Excel: 365 · Google Sheets: Supported

Worked examples

1. Total hours logged

Task	Owner	Start	Due	Hours
Design	Alice	2024-01-01	2024-01-05	8
Develop	Bob	2024-01-03	2024-01-10	15
Test	Alice	2024-01-06	2024-01-12	5

=REDUCE(0, INDEX({"Task","Owner","Start","Due","Hours";"Design","Alice","2024-01-01","2024-01-05",8;"Develop","Bob","2024-01-03","2024-01-10",15;"Test","Alice","2024-01-06","2024-01-12",5}, ,5), LAMBDA(acc,val, acc+val))

Result: 28

The INDEX call extracts the fifth column (Hours) as the array {8;15;5}. REDUCE starts with 0, then adds each hour value to the accumulator, yielding 0+8=8, 8+15=23, 23+5=28. The final result is the total hours across all tasks.

2. Latest due date

Task	Owner	Start	Due	Hours
Design	Alice	2024-01-01	2024-01-05	8
Develop	Bob	2024-01-03	2024-01-10	15
Test	Alice	2024-01-06	2024-01-12	5

=REDUCE(DATE(1900,1,1), INDEX({"Task","Owner","Start","Due","Hours";"Design","Alice","2024-01-01","2024-01-05",8;"Develop","Bob","2024-01-03","2024-01-10",15;"Test","Alice","2024-01-06","2024-01-12",5}, ,4), LAMBDA(acc,val, IF(val>acc, val, acc)))

Result: 2024-01-12

The fourth column (Due) supplies the dates {2024-01-05;2024-01-10;2024-01-12}. REDUCE begins with a very early sentinel date and keeps the larger of the accumulator and each new date. After processing all rows, the accumulator holds the latest due date, 12 January 2024.

3. List unique owners

Task	Owner	Start	Due	Hours
Design	Alice	2024-01-01	2024-01-05	8
Develop	Bob	2024-01-03	2024-01-10	15
Test	Alice	2024-01-06	2024-01-12	5

=REDUCE("", INDEX({"Task","Owner","Start","Due","Hours";"Design","Alice","2024-01-01","2024-01-05",8;"Develop","Bob","2024-01-03","2024-01-10",15;"Test","Alice","2024-01-06","2024-01-12",5}, ,2), LAMBDA(acc,val, IF(ISNUMBER(SEARCH(val,acc)), acc, IF(acc="", val, acc&", "&val))))

Result: Alice, Bob

The second column (Owner) yields {"Alice";"Bob";"Alice"}. REDUCE starts with an empty string. For each owner, the LAMBDA checks whether the name already appears in the accumulator using SEARCH; if not, it appends the name (adding a comma when needed). The final string contains each distinct owner exactly once.

Common errors

Which REDUCE error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The LAMBDA is defined with only one parameter, but REDUCE always passes two arguments (accumulator and current value).	Rewrite the LAMBDA to accept two arguments, e.g. LAMBDA(acc,val, …).
#SPILL!	The array argument is a spilled range that overlaps the cell containing the REDUCE formula, so the result cannot be written.	Move the formula to a cell that does not intersect the spill area, or reference the range with the @ operator to force a single-cell reference.
#N/A	One of the cells in the array argument contains a #N/A error; REDUCE propagates that error as soon as it reaches the offending element.	Wrap the array with IFERROR or FILTER to remove or replace error values before passing it to REDUCE.

Tips and when to use something else

Use LET to store the timesheet array once and reference it multiple times, keeping formulas tidy.
When you need every intermediate accumulator value (e.g., a running total), use SCAN instead of REDUCE.
For simple numeric aggregation like sum or max, the built-in SUM, MIN, or MAX functions are faster and clearer.
Combine REDUCE with BYROW or BYCOL when you want to aggregate across rows or columns while still applying a custom LAMBDA.

Related functions

LETLET lets you name a single intermediate result inside a formula, then return that result, making complex calculations clearer and easier to read.LAMBDALAMBDA lets you create custom, reusable functions directly in a cell, returning any value or array based on supplied parameters.SCANSCAN returns a spill array of accumulated results by applying a custom LAMBDA to each element of an input array, starting from an initial value.MAPMAP applies a custom LAMBDA to each element of a range, returning a new array of the same shape with the calculated results.BYCOLBYCOL applies a LAMBDA to each column of an array, returning a new array that aggregates or transforms column-wise results.

Frequently asked questions

How do I use REDUCE to concatenate text from a column?: Pass an empty string as the initial value, feed the target column as the array, and write a LAMBDA that adds the current text to the accumulator, inserting a delimiter only when the accumulator isn’t empty.
Can REDUCE return an array instead of a single value?: Yes, the accumulator can be an array, but the LAMBDA must return an array of the same dimensions on each iteration. This is useful for building up a matrix row-by-row, though SCAN is often a clearer choice for that pattern.
Why does REDUCE give a #VALUE! error when my formula works in Google Sheets but not in Excel?: Excel requires the LAMBDA to have exactly two parameters; Google Sheets is more forgiving and will ignore extra parameters. Ensure your LAMBDA signature matches (accumulator, current) to be portable.
Is REDUCE volatile like SUMIF or does it recalculate only when its inputs change?: REDUCE is non-volatile; it recalculates only when the initial value, array, or LAMBDA definition changes, making it efficient for large datasets.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17