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. For example, you can use the REDUCE function to calculate conditional sums and counts, similar to SUMIFS and COUNTIFS, but with more flexibility. In addition, by using a named LAMBDA with REDUCE, the logic can be defined and managed in one location and reused elsewhere.

The REDUCE function takes three arguments: initial_value, array, and lambda. Initial_value is the initial seed value to use for the final accumulated result. Initial_value is optional. Array is the array to reduce and lambda is a custom LAMBDA function to perform on each element of array that ultimately determines the final value returned by REDUCE.

LAMBDA structure

The calculation performed by the REDUCE function is determined by a custom LAMBDA function. The structure of the LAMBDA used inside of REDUCE looks like this:

LAMBDA(a,b,calculation)

The first argument, a, is the accumulator. The accumulator begins as the initial_value provided to REDUCE and changes as the REDUCE function loops over the elements in array and applies a calculation. The b argument represents the value of each element in array. Calculation is the formula that generates the final accumulated result.

Note: REDUCE returns a single result. See the MAP function to process each element in an array individually and return an array of results.

Examples

In the formula below, REDUCE is used to sum all values in an array:

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

In the LAMBDA function, a is the initial_value given to REDUCE (zero), and b represents the individual elements in array. The LAMBDA runs one time for each element in array, and at each iteration the current value of b is added to the accumulator. REDUCE returns 15 as a final result after all elements have been processed.

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,b,IF(ISEVEN(b),a+b,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 b represents the individual elements in array. The difference is the calculation inside the LAMBDA:

IF(ISEVEN(b),a+b,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,b,IF(ISODD(b),a+b,a)))

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