The Excel REDUCE function applies a custom LAMBDA function to each element of the given array and returns a single accumulated value. It is mostly used to calculate conditional sums and counts.
For example, suppose that you own an online store. You wish to know the total orders that are above $100. Refer to the following table:
Our formula will be,
=REDUCE(0, B2:B11, LAMBDA(count, order, IF(order>=100, count +1, count)))Where,
- 0 is the initial value/accumulator (starting count)
- B2:B11 is the array that includes the order value
- LAMBDA(count, order, IF(order>=100, count +1, count)) is the custom LAMBDA function to find the orders that are above $100
The results are displayed in the following image,
Syntax
The REDUCE is a simple function with the following syntax.
=REDUCE(initial_value, array, function)Where,
- initial_value is the initial value of the accumulator
- array needs to be replaced with the array to be reduced
- function needs to be replaced with the custom LAMBDA function
Important Notes:
- The LAMBDA function must include two parameters: the accumulator and the value.
- If the array is empty, the function returns the initial value (or accumulator).
How to use the REDUCE Function in Excel
Download the example spreadsheet used to demonstrate the REDUCE function in the upcoming section. Practice is the key to success!
Example 1: REDUCE Function Basics
Suppose that we are calculating total revenue based on our initial balance and last month’s sales. Our initial balance was $100,000. For the last month’s sale, refer to the following image.
We have:
- Product names in column A
- Sales in column B
Here are the steps,
- Select the cell B13
- Type =REDUCE
- Double-click the first option from the list
- Put “100000”
(It is the initial accumulator that represents our balance) - Type , on your keyboard
- Provide the cell reference B2:B11
(It contains our monthly sales generated by each product) - Type ,
- Next, type LAMBDA
(As the LAMBDA is a function in Excel, you will see the following popup) - Choose the first option from the popup
- Type balance as the first parameter for the LAMBDA function
- Type ,
- Put sale as the second parameter
- Now, replace the calculation argument of the LAMBDA function with balance + sale
- Complete the bracket for the LAMBDA function using )
- Complete the bracket for the REDUCE function using )
- Press the Enter key
Our final formula is as follows,
=REDUCE(100000, B2:B11, LAMBDA(balance, sale, balance + sale))Where,
- 100000 is the initial balance (acting as the initial accumulator)
- B2:B11 is the cell range or array on which the LAMBDA calculation will be performed
- LAMBDA(balance, sale, balance + sale) is the LAMBDA formula to calculate the total revenue
The results are displayed in the following image,
To learn more about the Excel LAMBDA function, click here: Learn Excel LAMBDA Function (Formula, Syntax, Examples)
Example 2: Combining REDUCE with Other Functions to Create Complex Formulas
Assume that you own an online store. You have the following orders. Their total value and shipping weight are in columns B and C, respectively..
Our task is to calculate the total shipping cost based on shipment weight. We have the following weight categories:
- Heavy: $10 if the shipping weight is above 4 kg
- Standard: $5 if the shipping weight is between 2 and 4 kg
- Small: $1 if the shipping weight is less than 2 kg
We will combine the LAMBDA with the IF function to calculate the total shipping cost.
Here are the steps,
- Click the cell B11
- Type =REDUCE
- Choose the first option from the popup
- Put 0 as the initial accumulator
- Type , to move to the next argument
- Provide the cell reference C2:C9
(It contains shipping weight for each order) - Type ,
- Next, put LAMBDA
(As the LAMBDA is a function in Excel, you will see the following popup) - Choose the first option from the popup
- Specify totalCost as the first parameter for the LAMBDA function
- Type ,
- Specify weight as the second parameter for the LAMBDA function
- Type ,
- Now, copy and paste the formula totalCost + IF(weight > 4, 10, IF(weight>2,5,1))
- Complete the bracket for the LAMBDA function using )
- Complete the bracket for the REDUCE function using )
- Hit the Enter key
Our final formula is as follows,
=REDUCE(0, C2:C9, LAMBDA(totalCost, weight, totalCost + IF(weight > 4, 10, IF(weight>2,5,1))))Where,
- 0 is the initial aggregator (acts as the initial shipping value)
- C2:C9 is the array or cell range holding the shipping weight
- totalCost + IF(weight > 4, 10, IF(weight>2,5,1)) is the formula to calculate the total shipping cost
The results are displayed in the following image,
Common Things to Consider While Using the REDUCE Function in Excel
The REDUCE function is an advanced alternative to traditional ones, including SUM and AVERAGE. It lets you apply a custom calculation across an array.
Here are the common mistakes to avoid while using REDUCE:
- Initial Value: The initial value plays a crucial role in the overall functioning of this formula. Most of the time, we tend to use zero as the initial value. However, in the case of complex formulas, it may take a numeric value.
- Using LET Function: When the LAMBDA formula becomes too complex, make sure to break it down using the LET function.
SCAN vs. REDUCE Function in Excel
Both functions perform a LAMBDA calculation on each element of the given array. The difference lies in their outputs:
- The REDUCE function returns a single value
- The SCAN function returns an array of values
Moreover, the syntax and functioning of both functions remain the same. To learn more about the SCAN function, click here.
Takeaway
The REDUCE is a logical function that helps you apply a custom calculation across an array and reduce it to a single output. You can solve complex problems, including conditional sums and counts. All you need to do is combine REDUCE with the other functions in Excel.
You may take some time to learn this function, but the effort is totally worth it. Make sure to practice the examples and refer to the explanation above until you master the REDUCE function in Excel.
That said, I hope this article taught you all the bells and whistles of the REDUCE function. Please comment below if you are stuck or encounter any particular error while using the REDUCE function. I will answer your questions as soon as possible.
Additional Resources:
- SCAN Function in Excel
- LAMBDA Function in Excel
- MAP Function in Excel
- IF Function in Excel
- AND Function in Excel
- OR Function in Excel
- NOT Function in Excel
Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.