Excel Conditional SUMPRODUCT

Full Video Transcript

Introduction to Conditional SUMPRODUCT

Conditional SUMPRODUCT uses SUMPRODUCT with conditions to exclude data that does not meet a certain criteria. You can use conditional SUMPRODUCT to extract the subset of the information that you want and exclude everything else.

Let's do a quick review of SUMPRODUCT. This is a topic we cover in level one. If we look at this table here, we have states that have a certain number of sales for products that have a specific unit price. The unit prices are all the same in this table.

Calculating Total Revenue the Traditional Way

Now I would like to get the total revenue for all the states. I can start that by typing in equals, then I'm going to select the sales, multiply that by the unit price, and press enter. So I have the total sales of New York which is $212.50. I'm not going to need to retype that formula. I can simply use autofill to come up with the answers or the results for the rest—the total revenue for California, Texas, Nevada, and then finally I'll go to the bottom and come up with the total.

If I go over to the Formulas tab, I can click AutoSum, press enter, and I have $1,012.50. So in order to come up with that answer, I needed to move over to another column, come up with four different function calculations, and then come up with a total.

Using SUMPRODUCT for Efficient Calculations

I can do that all in one cell using SUMPRODUCT. All I need to do is type equals S-U-M-P. At this point I can press Tab, and then I'm going to select the two columns that I want to multiply with each other. I'll start with sales, then I'll enter a comma, and then I'll select the unit price. All I have to do now after putting in a closed parenthesis is press enter, and when I press enter I will have the total.

Adding Conditions to Filter Data

Now what I would like to do is find the total just for Texas. This is where we're going to use conditional SUMPRODUCT to pull a subset of the information from the table. I'm going to head over to the cell where I'm going to get the total for Texas and I'm going to start to write the SUMPRODUCT function.

At this point you're going to do exactly what you did in the last exercise. Equals SUM P Tab, I'm going to select all the sales, enter a comma, and then select all the unit price. Enter a comma, and this is where things are different. I'm going to enter a double negative, then within the open parenthesis I'm going to select the entire state column and add an equal sign and say that I'm looking for in that column "TX" in double quotes. Close parentheses and then a final closing parenthesis to close off the SUMPRODUCT function.

I'll press enter. I get 350. Supposedly the total just for Texas is 350. Let me check to see if that is correct. All I need to do is delete everything else that isn't Texas, and let me just take a look. There we go, 350. So conditional SUMPRODUCT was able to correctly find the total for Texas.

Understanding the Double Negative Logic

Now if we want to figure out what's going on, what I'm going to do is go over to Evaluate Formula. This is one of the tools we can use to figure out what's going on behind the scenes in a function like this. I click evaluate and I see a little bit more information about the numbers and the values for the SUMPRODUCT function. I see the individual sales and unit prices, and I also see false, false, true, false.

That's something you'll see at the end of another very popular function in Excel, VLOOKUP. At the end of VLOOKUP you'll indicate whether or not you want an exact match or an approximate match, and you'll use either false or true. But you could also use another value, 0 or 1, and that will determine whether or not you want an exact match or an approximate match. False is zero and true is one.

I'm going to step out of this and let's actually go to the Insert Function dialog box, which is located right next to the formula bar. I'll click on the fx button. Here's what we're seeing going on behind the scenes: when I add the double negative, I'm actually turning all the trues and falses to zeros and ones, which is their other alias.

So when I'm multiplying values across the board, I'm either multiplying them with zero or one. Any values that are false for Texas get multiplied by zero and cancel out those values. But any state that matches TX in that column results in a one. Therefore I'm doing 75 times 2.5 times 1, and I keep the value of 75 times 2.5. I'll click cancel to get out of this dialog box.

Calculating Weighted Averages

That's one way you can use conditional SUMPRODUCT. You can also use it to come up with a weighted average. We have scores and numbers of students and whether they graduated or not. Let's find out the weighted average.

The function and formula that you have for this calculation is listed over to the right. That's going to be equal to SUMPRODUCT, and then we're going to select all the scores, comma, total number of students. I'm going to close that off and then divide that by the sum of the students. That will give me a weighted average for the entire student body. That's 77.30 and a lot of extra decimals. I'll go over to the Home tab and decrease the decimals.

Conditional Weighted Average for Graduates

Now I want to be able to come up with the conditional weighted average. I only want the weighted average for students who graduated. That's going to be equals SUMPRODUCT. Again, it's going to start the same. I'm going to select all the scores, comma, select all the students, and then I'm going to enter a comma, enter a double negative. In the open parentheses, I'm going to select the criteria range which is the graduating column, and I'm looking for values that are equal to, in double quotes, "Y". The first closing parenthesis closes off the parentheses that selects the graduating column, and then I'll use one more closing to close off SUMPRODUCT.

Now this all needs to get divided. Whatever the result here is needs to get divided by only the number of students who graduated. I'll divide that by SUMIFS. This is a function we went over in level two. We're going to use it here to get a count only for the students who graduated.

SUMIFS first—what am I going to sum? I'm going to sum the number of students. I'm going to enter a comma, and then I need to enter a criteria range. The criteria range is graduating. Then I'm going to enter a comma and enter the criteria. The criteria needs to be "Y". This is different than what we did in the SUMPRODUCT function. This is what you do in SUMIFS to identify the criteria for the criteria range. Then I'll press enter and I get 85.00.

Summary and Key Takeaways

So that is the conditional weighted average for students who graduated. It would make sense that this would be the conditional weighted average since the overall student body has their weighted averages dragged down by the students who didn't graduate, who got lower scores. Of course the score is going to be lower than if you just focus on the students who graduated, who have higher scores.

So that is conditional weighted averages and also how you can use conditional SUMPRODUCT to come up with it. We also took a look at how you can get a subset of data using conditional SUMPRODUCT as it relates to sales for numerous states.