REDUCE Function

The REDUCE function applies a custom LAMBDA function to each element in a given array and accumulates results to a single value. The REDUCE function is useful when you want to process each element in an array and return a single aggregated result. REDUCE is useful for creating custom calculations that Excel doesn't have built-in functions for, such as conditional sums, conditional counts, and other complex aggregations.

Like the SCAN function, REDUCE iterates over all elements in an array and performs a calculation on each element while updating the value of an accumulator. However, while SCAN returns an array of intermediate values, REDUCE returns a single final value.

Key features

• Reduce or aggregate values into a single result.
• Uses a custom LAMBDA function to apply calculations.
• Tracks the prior result of a calculation as an "accumulator".
• Complete control over how values accumulate.
• Good for conditional sums, counts, and other complex aggregations.
• Can replace complex formulas with more readable and maintainable code.

REDUCE returns a single aggregated result after processing all elements in an array. To process an array and return all intermediate values, see the SCAN function. To process each element in an array individually and return an array of transformed results, see the MAP function.

LAMBDA structure

The REDUCE function takes three arguments: initial_value, array, and function. Initial_value is an optional initial seed value to use for the accumulator. Array is the array to reduce, and function is typically a custom LAMBDA function to apply to each value in the array. The structure of the LAMBDA used inside REDUCE looks like this:

LAMBDA(a,v,calculation)

The first argument, a, is the accumulator. The accumulator begins as the initial_value provided to REDUCE and changes as the REDUCE function iterates over the elements in the array and applies a calculation. The v argument represents the value of each item in the array. The calculation is a formula that operates on the accumulator (a) and value (v). The result of the calculation defines the value of the accumulator for the next iteration, and the final result is the value of the accumulator after all elements have been processed. For example, in the formula below, REDUCE is used to sum all values in an array:

=REDUCE(0,{1,2,3,4,5},LAMBDA(a,v,a+v)) // returns 15

The initial_value is provided as zero, the array is hard-coded as the array constant {1,2,3,4,5}, and the calculation, LAMBDA(a,v,a+v), simply adds the accumulator and value. The table below shows how the accumulator value changes during each iteration when REDUCE processes the array {1,2,3,4,5} with an initial value of 0. Notice that the accumulator is updated with the result of the calculation at each iteration.

The final result returned by REDUCE is 15, which is the final value of the accumulator after all elements have been processed.

The REDUCE function always assigns the first argument of LAMBDA to the accumulator (a) and the second argument to the current value (v) from the array. This behavior is built into the function's design and cannot be changed. The names a and v are arbitrary, used to represent accumulator and value. You can use any names that make sense to you for a given use case.

As mentioned above, initial_value is optional. Although it does not appear in Excel's official documentation, it seems that if the initial value is omitted, it does not default to zero (0) or an empty string ("") as you might expect. Instead, REDUCE takes the first element of the array as the initial value, then iterates over the remaining elements of the array. In other words, if you omit the initial_value, Excel treats the first element of the array as the starting accumulator, and processing continues from the second element onward. To avoid confusion, it's a good idea to set the initial value explicitly in most cases. One possible exception is when using REDUCE plus VSTACK to stack 1-D arrays. In that case, using an empty string ("") for the initial value will result in an extra element in the final array, which you will need to remove later with the DROP function. Omitting the initial value avoids this additional step.

Sum even and odd numbers

In the worksheet shown, the formula in D5 sums the even numbers in the range B5:B16:

=REDUCE(0,B5:B16,LAMBDA(a,v,IF(ISEVEN(v),a+v,a)))

The overall structure of this formula is the same as the original formula above. The a argument is the accumulator, seeded with the value provided as initial_value to REDUCE, and v represents the individual elements in array. The difference is the calculation inside the LAMBDA:

IF(ISEVEN(v),a+v,a)

Here, the IF function is used with the ISEVEN function to make the sum conditional. Values in array are only added to the accumulator if they are even numbers. The final result returned by REDUCE is 56.

The formula in D6 is almost the same: 

=REDUCE(0,B5:B16,LAMBDA(a,v,IF(ISODD(v),a+v,a)))

The only difference is that the ISODD function is used instead of ISEVEN.