REDUCE

Excel Functions

Reduce an array

Syntax

EXCEL

=REDUCE([initial_value], array, function)

Arguments

Parameter	Description
`initial_value`	[optional] The initial value of the accumulator.
`array`	The array to be reduced.
`function`	The function or custom LAMBDA to apply.

Return Value

A single value

Details

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 handy for creating custom calculations that Excel doesn't have built-in functions for, such as conditional sums, conditional counts, and other complex aggregations. 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 in REDUCE looks like this: The first argument, a, is the accumulator. T

Examples

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 accumula

EXCEL

LAMBDA(a,v,calculation)

LAMBDA structure

The first argument, a, is the accumulator. The accumulator begins as the initial_value provided to REDUCE and changes as the REDUCE function iterates

EXCEL

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

REDUCE for a basic conditional sum

One way to use the REDUCE function is to create a conditional sum that uses custom logic that would be difficult with a built-in function like SUMIFS.

EXCEL

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

REDUCE for a basic conditional sum

Notice that we have provided an initial_value of zero (0) and the array is given as the range B5:B16. The LAMBDA calculation looks like this:

EXCEL

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

REDUCE for a basic conditional sum

To calculate a conditional sum of odd numbers, we can simply swap ISEVEN for ISODD:

EXCEL

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

REDUCE for a basic conditional sum

Finally, to illustrate what the same formula looks like without any conditional logic, the formula in cell D7 sums all numbers in the range B5:B16 lik

EXCEL

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

REDUCE

Syntax

Arguments

Return Value

Details

Examples

See Also