Sign in or start a free trial to avail of this feature.
9. Adding Multiple Conditions to SUMPRODUCT
In this lesson, we’ll see some more advanced uses of SUMPRODUCT. We’ll see how we can identify sales or revenue satisfying multiple conditions using a single SUMPRODUCT function.
Counting Orders from Two Customers (01:05)
In the last lesson, we counted orders from a single customer, Sarah. Here, we use SUMPRODUCT to count the number of orders coming from one of two customers: Sarah or Jimmy. To do this, we want the argument to SUMPRODUCT to be an array that is one for either of these customers and zero otherwise. We do this by checking if the customer is Sarah, checking if the customer is Jimmy, and adding the results, instead of multiplying. This creates a ray of ones and zeros where the value is one if either of the customer conditions is true.
Amount of Orders for Two Customers (02:24)
Once we have created a SUMPRODUCT formula that counts orders for the two customers, we can easily modify the formula to calculate the value of the orders. We simply multiply our array of zeros and ones by the order amount array.
In the previous lesson, we used SUMPRODUCT to replace COUNTIFS and SUMIFS. In this lesson, we'll create a more complex SUMPRODUCT formula that contains multiple conditions. In the previous lesson, you might remember that we used SUMPRODUCT to replace COUNTIFS and also SUMIFS.
In the COUNTIFS example, SUMPRODUCT checked the B2 to B24 array for the value Sarah's. Then it checked C2 to C24 for laptop.
Excel returns TRUE when these conditions are met, and when TRUE is multiplied by TRUE, we get a value of one. This happened three times, and so the SUMPRODUCT returned a value of three.
To replace SUMIFS, we simply use this formula to identify the three transactions that corresponded to Sarah's laptops, and then multiplied by the order dollar amount. Now we're going to move on to a more complicated transaction, where we count the number of Sarah's or Jimmy's orders.
To do this, again we write SUMPRODUCT, and we open a bracket, and select the customer array.
I will make sure that this equals to Sarah's.
Then we'll open another bracket, select the same array, and check does this equal to Jimmy's? You'll notice in this example that I've put a plus sign between the two conditions and not a multiplication.
In this example if TRUE is found in either condition, then the SUMPRODUCT will return one.
And as you can see, this gives me a value of nine.
So that in both cases, if either Sarah's or Jimmy's returns TRUE, SUMPRODUCT will grab those entries and SUM them at the end to give me the value of nine. To calculate the dollar amount of Sarah's or Jimmy's orders, it's very easy, we can simply take the previous formula copy again, escape, and place in the next cell.
We'll jump back into the formula, and simply multiply our conditions for the order dollar amount column.
Which I'll select and close the bracket.
I'll need to fix my brackets, which I'll do.
We place multiple brackets around these two conditions, and then close the SUMPRODUCT bracket as well.
This gives me a value of 298,000. As an exercise, see if you can complete these two final asks, where we combine Sarah or Jimmy's laptop orders for both the count and the order dollar amount.