Excel keeps rolling out sophisticated functions for advanced array manipulation, and I truly appreciate that. After all, who doesn’t want a better way to work in Excel? The REDUCE function, one of Excel’s new group of functions, is a perfect example. Its ability to collapse a range of values into a single accumulated result using custom LAMBDA functions almost feels like sci-fi. Yet, cleverness, as exciting as it is, isn’t always what I need in a spreadsheet.
That’s why I keep finding myself turning back to SUMPRODUCT far more often. It’s older, it’s simpler, and it has consistently proven to be the better choice in most of the tasks where I’d consider using REDUCE. And if your day-to-day work looks anything like mine, I suspect you might end up agreeing with me.
Why SUMPRODUCT over REDUCE
It handles complex logic in one step without needing a custom LAMBDA
Using REDUCE requires defining a LAMBDA with three required parameters: accumulator, value, and body. You can even supply an initial_value to set the accumulator’s starting point. All this structure gives REDUCE enormous flexibility. For instance, you can use it to calculate total cost (cost price multiplied by units sold), sum squared values (a+b^2), create customized functions (like summing only the values in a range that exceed 50), or even count only the even values within an array. It’s undeniably powerful.
=REDUCE(0, SEQUENCE(ROWS(G2:G20)), LAMBDA(total, row_num, total + INDEX(G2:G20, row_num) * INDEX(H2:H20, row_num)))
However, this sophistication also means that if you provide an invalid LAMBDA or the wrong number of parameters, you’ll end up with a #VALUE! error, one of Excel’s peskiest errors. Debugging it isn’t always straightforward, especially since LAMBDAs aren’t exactly the most intuitive functions to set up. Meanwhile, SUMPRODUCT is much easier both to write and to troubleshoot.
If you want to calculate your total cost using SUMPRODUCT, the formula is much simpler:
=SUMPRODUCT(G2:G20, H2:H20)
Excel multiplies the items in the first array (cost price) by the corresponding items in the second array (units sold) and then sums the results to give you the total. It is immediately clear what the formula does, which isn’t always the case when you use REDUCE for the same task.
There’s also the issue of availability. REDUCE only works in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, and Excel for the web. Anyone still using Excel 2016, 2019, 2021, or even 2024 won’t have access to it, which makes collaboration trickier than it needs to be.
This is exactly why, despite how clever REDUCE is, I still reach for SUMPRODUCT. It works consistently across a wider range of Excel versions and, more importantly, avoids the structural overhead that REDUCE demands. No LAMBDAs or complex parameters, and far fewer opportunities for something to go wrong.
How I use SUMPRODUCT instead of REDUCE
Calculations where SUMPRODUCT is simpler, clearer, and harder to break
Of course, SUMPRODUCT cannot perform everything REDUCE can. It is limited to numerical array arithmetic that ends with a summation. Still, it handles all the counting, simple conditional sums, multi-conditional sums, and weighted averages that REDUCE can manage, at least in cases that don’t require sequential logic.
SUMPRODUCT works in one consolidated operation (e.g., multiplying one array by another and summing the results) while REDUCE processes each step sequentially. That step-by-step approach is essential for calculations like running balances or cumulative totals, where the value in step B depends on what happened in step A. So, if your summation requires this kind of running logic, SUMPRODUCT won’t work. Otherwise, you’re good to go.
By design, SUMPRODUCT returns the sum of the products of corresponding ranges, so its default operation is multiplication, using this syntax:
=SUMPRODUCT(array1, [array2], ...)
However, you’re not limited to multiplication. By replacing commas with the arithmetic operators I need (*, /, +, -), I can perform addition, subtraction, or division across arrays. Suppose you want to find the total amount for which you’re over or under budget for the month across several expense categories. I’d use a formula like this:
=SUMPRODUCT((H2:H10 - I2:I10))
SUMPRODUCT subtracts my actual spend from my budgeted amount for each item (B2-C2, B3-C3, and so on), then sums those differences. If you get a positive result, it means you’re under budget overall. If not, you know you have some adjustments to make.
I could also go a step further and determine the total amount I overspent:
=SUMPRODUCT(--((H2:H10 - I2:I10)
This formula sums the absolute value of the negative difference between the two columns (200 from H3–I3, plus 300, 20, and 50 from the other rows), determining that the amount I overspent across all over-budget categories is $570.
I also use SUMPRODUCT to sum values based on multiple criteria. For example, I use it to calculate total revenue for products that meet several conditions, such as medium-sized, non-tissue paper items, or to count how many tissue papers the East region sold.
=SUMPRODUCT((A2:A10"Tissue Paper")*(C2:C10="Medium")*D2:D10)
=SUMPRODUCT((A2:A10="Tissue Paper")*(B2:B10="East"))
Instead of writing complicated nested IF statements, SUMPRODUCT allows me to layer conditions directly into the formula.
The one rule you must follow is that all arrays must share the same dimensions. If they don’t, SUMPRODUCT returns the #VALUE! error, too. Fortunately, this requirement is predictable and easy to check—certainly easier than debugging a miniature function built inside another function. For most spreadsheet tasks that end with a summation, especially those I need to share with others, SUMPRODUCT’s straightforward structure matters more than REDUCE’s elegance.
SUM was my go-to until I found this Excel function
SUM is basic. This is powerful.
Some old functions still solve the problem with less effort
REDUCE deserves credit for what it can do, especially if you work exclusively in the newest version of Excel and need highly customized array logic. Still, for most day-to-day work, and across far more versions of Excel, SUMPRODUCT continues to provide the stable, predictable results I need. And that reliability is why I continue to trust it.