Aggregate an Array with REDUCE

Excel 365LAMBDA

REDUCE boils a whole array down to a single value by carrying an accumulator through it — a custom sum, a concatenated string, a product. It’s the dynamic-array way to do what a loop would do in code.

Quick formula: to sum only the values over 100 in one expression:

=REDUCE(0, B2:B100, LAMBDA(acc, val, acc + (val>100)*val))

Start at 0; for each value, add it only when it’s over 100; return the final accumulated total.

Functions used (tap for the full reference guide):

REDUCE LAMBDA

The example

REDUCE collapses the list to one answer.

	A	B
1	Value
2	120
3	80
4	200
5	Sum of >100:	320

The formula

The conditional sum, done with REDUCE:

=REDUCE(0, A2:A4, LAMBDA(acc, val, acc + IF(val>100, val, 0))) // 120 + 200 = 320

How it works

REDUCE is a single-output accumulator:

The first argument is the starting value (0).
It walks A2:A4; the LAMBDA(acc, val, …) updates the accumulator with each item.
Here it adds the value only when it’s over 100, so the running result ends at 320.
Unlike SCAN, REDUCE returns only the final value — one number (or one string), not a spilled series.

REDUCE shines for custom aggregations that have no built-in function — building a delimited string, multiplying with conditions, or accumulating an array with VSTACK. For plain sums/counts, SUMIFS is simpler; reach for REDUCE when the logic is unusual.

Try it: interactive demo

Live demo

Set a threshold; REDUCE sums values above it from {120, 80, 200, 50}.

Sum values over

Result:

Variations

Build a delimited string

Concatenate items with REDUCE:

=REDUCE("", B2:B10, LAMBDA(a, v, a & v & ", "))

Product of a range

Multiply everything together:

=REDUCE(1, B2:B10, LAMBDA(a, v, a * v))

Stack arrays into one

Combine per-row arrays vertically:

=REDUCE("", seq, LAMBDA(a, v, VSTACK(a, fx(v))))

Pitfalls & errors

REDUCE returns one value. If you want the running series at each step, use SCAN instead.

Excel 365 only. Older versions lack LAMBDA helpers — use SUMIFS/SUMPRODUCT for the common cases.

Watch the seed type. Start with "" for string building, 0 for sums, 1 for products — the wrong seed type errors or skews the result.

Practice workbook

📊

Download the free Aggregate an Array with REDUCE practice workbook

REDUCE conditional-sum, string-build, and product examples (results shown), plus 4 challenges with answers. No sign-up required.

Frequently asked questions

What does the REDUCE function do in Excel?

REDUCE collapses an array to a single value by carrying an accumulator through it with a LAMBDA: =REDUCE(start, array, LAMBDA(acc, val, ...)). It returns only the final accumulated result. Requires Excel 365.

What's the difference between REDUCE and SCAN?

REDUCE returns only the final value; SCAN spills every intermediate value as a running series. Use REDUCE for one aggregate, SCAN for a running column.

When should I use REDUCE instead of SUMIFS?

Use REDUCE for custom aggregations with no built-in function, like building a delimited string or multiplying with conditions. For ordinary conditional sums, SUMIFS is simpler.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: REDUCE · LAMBDA