The REDUCE function in Google Sheets is a powerful tool designed to condense an array into a single accumulated result. By leveraging the functionality of LAMBDA, this function allows for custom aggregation processes, making data calculations easier and more efficient.

Syntax

REDUCE(initial_value, array, LAMBDA(accumulator, current_value, ...))

• initial_value: The starting point for the accumulation process.
• array: A range of values or array that you want to reduce.
• LAMBDA: A function that takes an accumulator and the current value, performing a specified operation on them.

Example #1

=REDUCE(0, A1:A5, LAMBDA(total, current, total + current))

This will sum up the values in the range A1 to A5, starting with an initial value of 0. For example, if A1:A5 contains 1, 2, 3, 4, and 5, the result will be 15.

Example #2

=REDUCE("", B1:B3, LAMBDA(accum, current, accum & current))

This concatenates all the strings in the range B1 to B3. For B1:B3 containing “Hello”, ” ” (space), and “World!”, the output will be “Hello World!”.

Example #3

=REDUCE(1, C1:C4, LAMBDA(product, current, product  current))

This multiplies all the values in the range C1 to C4, starting with an initial value of 1. If C1:C4 has values 2, 3, 4, and 5, the result will be 120 (2 3 4 5).

Error handling

• VALUE!: This error indicates that the provided parameters are not valid, often arising when the accumulator or current_value is not compatible with the expected data type.
• REF!: This error is shown when the function refers to an invalid range, typically due to deleted cells or ranges outside the bounds of the data.
• NAME?: This signifies that the LAMBDA function name is not recognized, which may occur if the function was mistyped or the LAMBDA term is not defined properly.

Conclusion

Incorporating the REDUCE function in Google Sheets can significantly elevate your data processing efficiency, allowing for tailored calculations that address specific needs. By mastering its syntax and understanding how to respond to common errors, users can leverage this function to transform and analyze their data more effectively.