Excel Conditional SUMPRODUCT

The SUMPRODUCT Function and other essential Excel functions are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

SUMPRODUCT Review

So let's do a quick review of SUMPRODUCT. This is a topic we cover in Level 1. 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. Unit prices are all the same in this table. Now, I would like to get the total revenue for all the states, so I can start that by typing in equal, then I'm going to select the sales, multiply that by the unit price and press ENTER.

So I have the total sales for 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 Texas, 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. I have $1,012.50.

So to come up with that answer, I needed to move over to another column, come up with four different functions, calculations, and then come up with a total. I can do that all in one cell using SUMPRODUCT. All I need to do is type equal, SUMP. 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.

Conditional SUMPRODUCT

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. So 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. So equal, SUMP, 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 parenthesis, 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 accurate. All I need to do is delete everything else that is in Texas. Let me just take a look, and there we go, 350. So conditional SUMPRODUCT was able to correctly find the total for Texas.

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.

So what I'm going to do is click Evaluate. When I click Evaluate, 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. So 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, zero or one. And that will determine whether or not you want an exact match or an approximate match. False is zero and true is one.

So 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 Effects button. And here's what we're seeing is 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. When I'm multiplying values across the board, I'm either multiplying them with zero or one. So 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 multiplying 75 times 2.5 times 1, and I keep the value of 75 times 2.5.

So I'll click cancel to get out of this dialog box. That's one way you can use conditional SUMPRODUCT.

Weighted Average

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. So let's find out the weighted average.

Now the function 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.

OK, so now I want to be able to come up with the conditional weighted average. I only want the weighted average for students who graduated. So that's going to be equal to SUMPRODUCT. But 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 and enter a double negative.

In the open parenthesis, 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 red parenthesis that selects the graduating column, and then I'll use one more closing parentheses 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. So I'll divide that by SUMIFS. This is a function we went over in Level 2. We're going to use it here to get a count only for the students who graduated.

So 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. Criteria range is "Graduating." Then I'm going to enter a comma, and then I'm going to enter the criteria. The criteria needs to be "Y." This is different from what we did in the SUMPRODUCT function. This is what you do in SUMIFS to identify the criteria for the criteria range. And then I'll press ENTER, and I get 85.00.

So that is the conditional weighted average for students who graduated, and it would make sense that that 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. So of course, the score is going to be lower than if you just focus on the students who graduated who have higher scores.

Recap

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.