Sign in or start a free trial to avail of this feature.
8. Using SUMPRODUCT for Conditional Arithmetic
SUMPRODUCT is a highly versatile formula that can be used in a number of different situations. In this lesson, we’ll see how SUMPRODUCT can be used to perform conditional arithmetic, letting us calculate revenue for particular customer and products.
The SUMPRODUCT Function (00:16)
SUMPRODUCT is a versatile function that multiplies corresponding entries in multiple arrays, then adds the results together.
As a simple example, we have an array of product prices and an array of order quantities. We can use SUMPRODUCT to find the total revenue. SUMPRODUCT multiplies each price by the corresponding quantity to find the revenue for each row, then sums these revenue figures together to produce the total revenue with a single formula.
Conditional Counting (01:23)
SUMPRODUCT can be used in situations where you could also use COUNTIF or COUNTIFS. For example, we might want to count the orders which are for a particular customer (Sarah) and a particular product (Laptop). We can use SUMPRODUCT to do this.
In this case, the argument to SUMPRODUCT is an array which will be 1 for the desired outcome and 0 otherwise. We get this by checking if the customer is Sarah and checking if the product is Laptop. This produces an array of TRUE/FALSE values. Excel treats True as 1 and False as 0, so multiplying the two values gives us the desired outcome.
Conditional Sums (03:10)
We can also use SUMPRODUCT to replicate SUMIF or SUMIFS. This is easy to do once we have the SUMPRODUCT formula from the previous section. We simply modify the formula to multiply the array of ones and zeros by the array of order amounts. This means that we have an array featuring order amounts when the desired criteria is met, and zero otherwise. SUMPRODUCT adds these order amounts together to produce the total revenue for the orders of interest.
In the previous lesson we looked at various applications of array formulas.
In this lesson, we'll learn how to use the SUMPRODUCT function to perform conditional arithmetic. The SUMPRODUCT function, is a useful function that can be often used when working with the arrays. SUMPRODUCT multiples corresponding components in a series of arrays and then sums the result of these products together. This probably doesn't sound very useful, but SUMPRODUCT is very versatile, and it can be used in many situations, including as an alternative to countifs and sumifs, which you may have seen already. Let's start with a simple example, and use SUMPRODUCT to calculate total revenue. So I'll write equals SUMPRODUCT and for the first array I'll simply select the product price.
For the second array, I'll select the order quantity.
I'll close brackets, and press enter. And here SUMPRODUCT multiples the product price by the product quantity for each row, then sums the answer together, to give me the correct total revenue.
Now let's move on to something a bit more challenging, and we're going to use SUMPRODUCT to find the number of laptop orders that the customer Sarah's has made. I'll start by writing SUMPRODUCT, I'll select the customer array, and I'll make sure it's equal to Sarah's.
And I'll multiply this by another array, product type and make sure this equals the laptop.
And in both cases, I put double quotes around each string, then close the brackets, and press enter. And when I do this, my formula tells me that Sarah's made three laptop orders in the first quarter.
Let's now explore this equation in more detail to figure out how it works.
For each row, we are checking that the customer name and the product type provides our desired value, Sarah's and then laptop.
For each cell, this returns a value of true or false. True if the customer is Sarah, and true if the product type is laptop, otherwise it returns false.
Excel treats false as a zero and true as a value of one.
And so in our SUMPRODUCT calculation, if we find a row where the customer is Sarah's and the product type is laptop, it provides us with a value of one. This happens three times in our data source, and SUMPRODUCT simply adds the three ones together to provide us with the number three.
In this example, we used SUMPRODUCT to replicate countifs, we can modidy this formula, so that it replaces sumifs instead. To do this, I'm going to copy the formula by jumping back into my cell, selecting the formula and control C to copy and then in the cell below I'll press control V to paste. And in this example, what I want to do is to find the dollar amount of Sarah's laptop orders, rather than the number of laptop orders. And to do this in my formula, all I need to do is include another multiplication sign, and include the order dollar amount array.
So I'll press F2 to jump out of the formula, and the order dollar amount array, and select it.
I'll make sure there's a bracket around this array, and press enter.
And when I do this, I can see that the amount of Sarah's laptop orders is 132,000. In my formula, all I'm doing is finding each row where there's a laptop order for Sarah's and multiplying the one that I calculated earlier, by the order dollar amount, and then SUMPRODCUT is adding together these order dollar amounts, at the very end. Now let's move on to a more complicated calculation, to count Sarah or Jimmy's orders, but I'll do this in the next lesson.