Master the power of Excel's conditional Sum Product function to efficiently calculate totals and weighted averages with precision. Learn practical techniques to isolate specific data subsets and simplify complex computations within a single cell.
Key Insights
- Utilize the conditional Sum Product function in Excel to isolate and calculate data subsets efficiently, such as computing total sales revenue specifically for Texas, by converting conditions into numerical values (zeros and ones).
- Apply conditional Sum Product to effectively calculate weighted averages, such as determining the average scores of graduating students exclusively, by integrating criteria within the function to include only relevant data.
- Leverage Excel's Evaluate Formula feature and Insert Function dialog box to analyze and understand the internal workings of complex conditional Sum Product calculations, enhancing your Excel proficiency and troubleshooting capabilities.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Conditional sum product. Using sum product with conditions to exclude data that does not meet a certain criteria. You can use conditional sum product to extract a subset of the information that you want and exclude everything else.
So let's do a quick review of sum product. 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 price 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 in 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 in order 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 Sum Product.
All I need to do is type equal, 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 the closed parentheses, is press ENTER. And when I press ENTER, I will have the total.
Now what I would like to do is find the total just for Texas. This is where we're going to use Conditional Sum Product 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 Sum Product function. At this point, you're going to do exactly what you did in the last exercise. So equal, S-U-M-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 parentheses, I am 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 parentheses to close off the Sum Product 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 isn't Texas. And let me just take a look. And there we go.
350, 350. So Conditional Sum Product 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 Sum Product 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 FX button, and here is 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 anything that is any state that matches TX in that column results in a one. Therefore, I'm multiplying 75 times 2.5 times one, 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 sum product. 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 sum product, 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. Okay, 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 sum product. 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 parentheses closes off the red parentheses that selects the graduating column, and then I'll use one more closing parentheses to close off sum product. 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 sum ifs. 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 sum ifs.
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 then I'm going to enter the criteria. The criteria needs to be Y. This is different than what we did in the sum product function. This is what you do in sum ifs 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.
So that is conditional weighted averages and also how you can use conditional sum product to come up with it. We also took a look at how you can get a subset of data using conditional sum product as it relates to sales for numerous states.