Microsoft Office Training London 020 79209500
The Excel SUMPRODUCT function is one of those hero functions that, once discovered, you wonder how you ever managed without. It does not just do “what it says on the tin”, there’s a lot more to it than that. In particular, although it’s not obvious, it’s actually one of the most powerful and flexible filter functions in Excel. This means that all those formulas where you would normally use SUMIF or SUMIFS can be done with SUMPRODUCT. And they can be done so much easier!
The usual implementation of SUMPRODUCT is to calculate the SUM of the products of multiple ranges. In plain English, “give me the total of this range multiplied by that range”. You can have more than just two ranges.
In this example we want to multiply the Quantity by the Price and then SUM the results. Usually you would create a helper column, as we have done here in column D, where you multiplied the values in column B by column C and copied the formula down. Then SUM the results.
There is nothing the matter with this method, it’s perfectly understandable and straightforward. But you don’t really need the helper column.
By using SUMPRODUCT you can make the calculation directly without needing the extra, helper column. Simply give me the total of the first range multiplied by the second. The function does the cross-calculation for each row and then returns the cumulative total.
=SUMPRODUCT(B2:B8,C2:C8)
Fantastic, but not for everyday. Like many Excel functions, it’s rare that you actually need to do that sort of calculation.
But SUMPRODUCT can also be used as a filter function. And, of course, these are the formulas that we do everyday. In this role, the function works on TRUE/FALSE values and its much faster, more efficient and more flexible than its SUMIF or SUMIFS equivalents. Although most people will find the formulas much harder to understand. In our example below, we need to add up the numbers in column B where we have “Red” in column A. Most people would use either SUMIF or SUMIFS for this.
Here’s the SUMPRODUCT formula to add the numbers in column B where there is “Red” in column A:
=SUMPRODUCT(- -(A2:A8=”Red”),B2:B8)
Or you use a simpler expression by simply multiplying the two ranges:
=SUMPRODUCT((A2:A8=”Red”)*B2:B8)
Answer is 1156. Values are 548+369+239.
Don’t forget the double minus trick and the brackets around the expression. The two minus signs coerce the logical values to ones and zeros. If you select the cell where you have the formula you can see how it works internally by clicking Formulas, Evaluate Formula then click the Evaluate button. The first array evaluates to Boolean TRUE and FALSE values. You will see that there are three TRUE values, the three cases where we have “Red” in A2:A8. This TRUE then evaluates to numeric 1 and is used to multiply the values in the B2:B8 range and then, finally, return the total.
But you don’t need to know how it works internally, you just want it to do your calculations. So, whenever you need it, you just do a copy and paste. Once you have working examples of viable formulas you can easily adapt them to your own needs. I am going to stick to the minus, minus form for most of the subsequent examples, because I think it keeps the Boolean expression clearer, especially where it is complex.
You can use numeric expressions with comparative operators, of course. And the same range may be referenced more than once. In this example, we want to add up the values in column B which are 500 or more.
=SUMPRODUCT((B2:B8>=500)*B2:B8)
Answer is 1648. Values are 548+524+576.
If you need a more complex filter, where you have more than one condition to satisfy, then multiply your range references. And use the minus, minus form of the formula. Here we need to calculate the total of the values in our Quantity column B where column A is “Red” and column B is 250 or more.
=SUMPRODUCT(- -(A2:A8=”Red”)*(B2:B8>=250),B2:B8)
Answer is 917. Values are 548+369.
In the previous example, the logical form was an AND. The value in column A must be “Red” AND the value in column B must be 250 or more. Both conditions must be satisfied.
Use Multiplication for a logical AND form. Use Plus for a logical OR. Add the values in column B where it is “Red” or “Green” in column A:
=SUMPRODUCT(- -(A2:A8=”Red”)+(A2:A8=”Green”),B2:B8)
Answer is 2256. Values are 548+524+369+576+239.
And you can combine the two logical forms AND and OR. Always using the standard convention, AND is Multiply, OR is Plus. In this example, we must add the values in column B where column A is “Red” or “Green” and column C is the value of 3 or more.
=SUMPRODUCT(- -((A2:A8=”Red”)+(A2:A8=”Green”))*(C2:C8>=3),B2:B8)
Answer is 1100. Values are 524+576. Only “Green” values are calculated as all the Prices for “Red” are under 3.
There’s no real restriction to the number of conditions which you can incorporate into your formula. Just keep everything inside brackets. In the final example the column A value is “Red”, “Green” or “Yellow” and column C is 3 or more:
=SUMPRODUCT(- -((A2:A8=”Red”)+(A2:A8=”Green”)+(A2:A8=”Yellow”))*(C2:C8>=3),B2:B8)
Answer is 1539. Values are 524+439+576.
If you ever need a negative expression in your formula, use the inequality operator (<>) or the NOT function. For example, not equal to “Red” is either (A2:A8<>”Red”) or (NOT(A2:A8=”Red”)).
I think we’re getting the general idea. You only need a few working examples that you can substitute into. The more complicated formulas with multiple conditions are much easier to do than their corresponding SUMIF and SUMIFS equivalents. I like the other functions and, it has to said, they are far more commonly used than SUMPRODUCT. But, and it’s a big but, while SUMIF and SUMIFS are great for simple criteria, as soon as you need anything complicated, they become both clunky and difficult. Whereas SUMPRODUCT will easily accept the most complex filters.
To get the best out of Excel formulas you’ll need to have a good, working knowledge of Excel functions. Come along on our dedicated Excel functions course and discover what Excel formulas can do for you.
Call 020 7920 9500 now for details.
It’s easy to calculate someone’s age from their date of birth if you know about Excel’s DATEDIF function, unfortunately it’s easy to miss this function as it is not documented. Excel will not help you fill in the DATEDIF function interval values, you need to see the list here.
Our Excel double click tricks are some of those little things that make your life so much easier. You probably know most of them already. Or do you? I think that anyone who uses Excel regularly should know them.
Complicated text formulas using either ampersands and the CONCATENATE function are the bane of our life. Not any more! Excel new text functions will really help us nail those text formulas. We’ll be looking at the CONCAT function and the TEXTJOIN function.
Usually the formulas you need for percentages and differences are quite straightforward: divisions for percentages and a minus sign to take one value from another. But there are pitfalls for the unwary which we shall explore.
I think the Excel FILTER function does the filter job better than AutoFilter. It’s a live formula and an extraction, you don’t have to filter your data in place. There’s no need for that clunky Advanced Filter…
You can use the Excel UNIQUE function to extract a list of unique or distinct items from a range of Excel cell values. It’s really easy. See how to sort the results automatically when the data changes…
