Table of Contents
The REDUCE function in Google Sheets allows users to easily combine data from multiple columns into a single cell. It simplifies the process of consolidating and organizing data, making it more efficient and convenient. By using this function, users can quickly and accurately create a comprehensive overview of their data, saving time and effort. This feature is particularly useful for managing large datasets or creating reports, as it eliminates the need for manual data entry and reduces the risk of errors. Overall, the REDUCE function in Google Sheets is a powerful tool for streamlining data management tasks and improving productivity.
REDUCE function
This function reduces an array to an accumulated result by application of a LAMBDA function to each value.
Sample Usage
REDUCE(5, A1:A3, LAMBDA(accumulator, current_value, accumulator+current_value))
REDUCE(2, A1:A3, LAMBDA(accumulator, current_value, accumulator*current_value))
Syntax
REDUCE(initial_value, array_or_range, LAMBDA)
initial_value: The initialaccumulatorvalue.array_or_range: An array or range to be reduced.LAMBDA: ALAMBDAthat’s applied to each value inarray_or_rangeto reduce it.- Syntax:
LAMBDA(name1, name2, formula_expression) - Requirements:
- The
LAMBDAmust have exactly 2namearguments along with aformula_expressionwhich uses thosenames. Thename1resolves to the current value in theaccumulatorandname2resolves to thecurrent_valueinarray_or_range, when applying theLAMBDA. Theaccumulatoris updated in each step to the intermediate value obtained in the previous step. Go to simple multiplication operation example.
- The
Notes
- The passed
LAMBDAshould accept exactly 2namearguments, otherwise an#N/Aerror is returned. These arguments correspond toaccumulatorandcurrent_value, in order. These are explained as:name1: Resolves to the current value in theaccumulator.name2: Resolves to thecurrent_valuein the input array.
- The
accumulatoris initialized byinitial_valueand updated in each step to the intermediate value obtained in the previous step.
- The
current_valuein the input array are found row by row, while theLAMBDAis being applied.
- A
named functioncan be passed for theLAMBDAparameter and behaves like aLAMBDAin this case. Learn more about named functions.- The
named functionmust follow theLAMBDAsyntax forREDUCEwith exactly 2 argument placeholders defined for it. Parenthesis shouldn’t follow the
named function.
- The
Examples
Simple multiplication operation
Return the product of all elements in A1:A3 and initial_value.
Example data:
A | |
|---|---|
1 | 3 |
2 | 2 |
3 | 4 |
Example:=REDUCE(5, A1:A3, LAMBDA(accumulator, current_value, accumulator*current_value))
How it works:
Initially, accumulator = 5
1. Processing cell A1:
|
After processing cell A1:
|
2. Processing cell A2:
|
After processing cell A2:
|
3. Processing cell A3:
|
After processing cell A3:
|
Result:
120 |
Sum if price is greater than or equal to $20
Add all the prices that are greater than or equal to $20.
Example Data:
A | |
|---|---|
1 | $50 |
2 | $10 |
3 | $30 |
4 | $20 |
Example:=REDUCE(0, A1:A4, LAMBDA(accumulator, price, if(price>=20, accumulator + price, accumulator)))
Result:
$100 |
Use a named function as LAMBDA function
Return the end price after increasing it by a certain percentage every period.
Make a Copy
Example Data:
A | B | C | |
|---|---|---|---|
1 | 2022 | 10% | Starting Price: |
2 | 2023 | 5% | $100 |
3 | 2024 | 5% | |
4 | 2025 | 10% |
Example: =REDUCE(C2,B1:B4,PRICE_INCREASE)
Named function:PRICE_INCREASE is a named function which outputs the result after increasing by the percentage value in column B.
Formula definition:=accumulator+accumulator*cell where accumulator and cell are argument placeholders defined for PRICE_INCREASE.
Result:
133.4 |
Use a named function as LAMBDA with 2-dimensional dataset
Find the list of unique employees of the quarter, preserving row-wise order.
Make a Copy
Example data:
A | B | C | |||
|---|---|---|---|---|---|
1 | Q1 | Q2 | Q3 | Q4 | |
2 | 2020 | John | Adam | Stacy | Adam |
3 | 2021 | Peter | Maurice | John | Kimberly |
4 | 2022 | Stacy | Michael | Peter | Adam |
Named function:ADD_IF_NOT_PRESENT is a named function which adds a given string value to an array of values.
Function definition:=IF(CONTAINS(new_value, existing_values), existing_values, {existing_values, new_value}), where existing_values and new_value are argument placeholders defined for ADD_IF_NOT_PRESENT in that order, and CONTAINS is another named function.
Example: =REDUCE({B2}, B2:E4, ADD_IF_NOT_PRESENT)
Result:
John | Adam | Stacy | Peter | Maurice | Kimberly | Michael |
Common Errors
The passed LAMBDA doesn’t have exactly 2 name arguments
If the LAMBDA function doesn’t have 2 name arguments, this error occurs:
“Wrong number of arguments to LAMBDA. Expected 3 arguments, but got 2 arguments.”
Example:=REDUCE(5, C1:C4, LAMBDA(current_value, current_value+1))
In this example, LAMBDA was given only 1 name argument when it needed 2.
The last parameter of REDUCE wasn’t a LAMBDA
If the last parameter of REDUCE function wasn’t a LAMBDA function, this error occurs:
“Argument must be a LAMBDA.”
Example:=REDUCE(5, C1:C4, 3)
In this example the last function is 3, instead of a LAMBDA function.
The LAMBDA passed to REDUCE was incorrect
If 1 or more name arguments aren’t valid, this error occurs:
“Argument 1 of function LAMBDA is not a valid name.”
Example:=REDUCE(5, C1:C4, LAMBDA(C1, v, C1+v))
In this example, C1 is an invalid name since it clashes with a range.
Related functions
- LAMBDA function: This function lets create and return a custom function with a set of
namesand aformula_expressionthat uses them. - MAP function: This function maps each value in the given arrays to a new value.
- BYROW function: This function groups an array by rows.
- BYCOL function: This function groups an array by columns.
- SCAN function: This function scans an array and produces intermediate values.
- MAKEARRAY function: This function creates a calculated array of specified dimensions.
- Create & use named functions: This function lets you create and store custom functions, similar to
LAMBDA.
Cite this article
stats writer (2024). “How can I use the REDUCE function in Google Sheets to combine data from multiple columns into a single cell?”. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-reduce-function-in-google-sheets-to-combine-data-from-multiple-columns-into-a-single-cell/
stats writer. "“How can I use the REDUCE function in Google Sheets to combine data from multiple columns into a single cell?”." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-reduce-function-in-google-sheets-to-combine-data-from-multiple-columns-into-a-single-cell/.
stats writer. "“How can I use the REDUCE function in Google Sheets to combine data from multiple columns into a single cell?”." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-reduce-function-in-google-sheets-to-combine-data-from-multiple-columns-into-a-single-cell/.
stats writer (2024) '“How can I use the REDUCE function in Google Sheets to combine data from multiple columns into a single cell?”', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-reduce-function-in-google-sheets-to-combine-data-from-multiple-columns-into-a-single-cell/.
[1] stats writer, "“How can I use the REDUCE function in Google Sheets to combine data from multiple columns into a single cell?”," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. “How can I use the REDUCE function in Google Sheets to combine data from multiple columns into a single cell?”. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.