Today, in Episode #1402, Bill takes a look at Calculating a Weighted Average using two (2) SUMIF Functions. This Formula will need to reference Data on another worksheet as well. Learn about "SUMIF"! **The Learn Excel from MrExcel Podcast Series.**
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL Learn Excel form MrExcel podcast, episode 1402.
SUMIF.
Hey, alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
This question sent in...
Data here on sheet 2, they wanna be able to choose a company and sheet to get the quantity and then the average unit price.
But we need a weighted average unit price because there's a different number of purchases and it's funny I've looked at the spreadsheet and here we have a SUMIF formula and that works great to get the the quantity.
They're actually building a true SUMIF but then for the total price, we're going back to hard coding values and so I looked at this and said, Okay!
Somebody, who created this worksheet understand SUMIF, they then handed it off or they left the company and someone else is left and trying to figure out total price, it's as if we don't really even understand what SUMIF is doing, all right!
So, let's take a look at the the SUMIF function here.
SUMIF A2 to A15, that says hey! Cruise down through all of these values in column A and see if it's equal to E2, Microsoft.
If it is, we need the corresponding value from B2 to B15.
All right! So, it'll add up in this case that 16 and 13 and that 19, to get the total.
So, if we have that formula working we can copy that and I'm gonna type an equal sign here and then paste [ ctrl V ].
That gives me two equal signs.
Yeah! That's just me, being silly because I don't like to have to retype the whole thing A2 to A15 and we, this time when I look and see if it's equal to E2.
But we want to add up the corresponding value from D.
So, I take those B's and change them to D.
All right! So, there's our answer.
You know, now the advantage here is if we add a new set of data, those will update without having to come back and re-type these formulas.
That's a much better way to go and then the average here, just doing the simple division.
Now, back on this page, what we need to do is we need to come up with an average unit price.
Well.
Well, there's a couple of different ways to go.
Let's just build it here, equal SUMIF and we can point to data on another worksheet.
So, I'm gonna click on Sheet 1 and choose everything in column A, A 2 to A15, I'll press [ F4 ] F4 puts the dollar signs in, so that way that formula remains absolute.
I want to see if that's equal to, here on sheet 2, when the C is equal to the name that would type into A2 comma and then if it is, we want to grab the corresponding value from column D and again, press [ F4 ].
Alright! So, that's the first half of our formula that gives us the total revenue.
Now, we want to divide by the quantity.
You know, actually boy this point.
I really have almost what I need here.
I'm gonna [ ctrl C ], to copy.
[ Ctrl V ], to paste and I realize we're outside of the window here.
I'm gonna come over to where it says D2, change that to B2 and change D15 to B15.
Press [ Enter ], let's just do a little test here.
We have our formulas are working here.
So, Java 4.438.
Java 4.438, let's type in Microsoft.
62 and 62 then final test of Microsoft, 20 yet.
Let's just go at 50.
Microsoft updates here 58.49 and updates their 58.49.
So, it's all working.
Alright! So, relatively simple formula admittedly, a very long formula.
With two SUMIFs, but once you understand what this SUMIF is doing then everything else just falls into place.
Well, hey! I wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
SUMIF.
Hey, alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
This question sent in...
Data here on sheet 2, they wanna be able to choose a company and sheet to get the quantity and then the average unit price.
But we need a weighted average unit price because there's a different number of purchases and it's funny I've looked at the spreadsheet and here we have a SUMIF formula and that works great to get the the quantity.
They're actually building a true SUMIF but then for the total price, we're going back to hard coding values and so I looked at this and said, Okay!
Somebody, who created this worksheet understand SUMIF, they then handed it off or they left the company and someone else is left and trying to figure out total price, it's as if we don't really even understand what SUMIF is doing, all right!
So, let's take a look at the the SUMIF function here.
SUMIF A2 to A15, that says hey! Cruise down through all of these values in column A and see if it's equal to E2, Microsoft.
If it is, we need the corresponding value from B2 to B15.
All right! So, it'll add up in this case that 16 and 13 and that 19, to get the total.
So, if we have that formula working we can copy that and I'm gonna type an equal sign here and then paste [ ctrl V ].
That gives me two equal signs.
Yeah! That's just me, being silly because I don't like to have to retype the whole thing A2 to A15 and we, this time when I look and see if it's equal to E2.
But we want to add up the corresponding value from D.
So, I take those B's and change them to D.
All right! So, there's our answer.
You know, now the advantage here is if we add a new set of data, those will update without having to come back and re-type these formulas.
That's a much better way to go and then the average here, just doing the simple division.
Now, back on this page, what we need to do is we need to come up with an average unit price.
Well.
Well, there's a couple of different ways to go.
Let's just build it here, equal SUMIF and we can point to data on another worksheet.
So, I'm gonna click on Sheet 1 and choose everything in column A, A 2 to A15, I'll press [ F4 ] F4 puts the dollar signs in, so that way that formula remains absolute.
I want to see if that's equal to, here on sheet 2, when the C is equal to the name that would type into A2 comma and then if it is, we want to grab the corresponding value from column D and again, press [ F4 ].
Alright! So, that's the first half of our formula that gives us the total revenue.
Now, we want to divide by the quantity.
You know, actually boy this point.
I really have almost what I need here.
I'm gonna [ ctrl C ], to copy.
[ Ctrl V ], to paste and I realize we're outside of the window here.
I'm gonna come over to where it says D2, change that to B2 and change D15 to B15.
Press [ Enter ], let's just do a little test here.
We have our formulas are working here.
So, Java 4.438.
Java 4.438, let's type in Microsoft.
62 and 62 then final test of Microsoft, 20 yet.
Let's just go at 50.
Microsoft updates here 58.49 and updates their 58.49.
So, it's all working.
Alright! So, relatively simple formula admittedly, a very long formula.
With two SUMIFs, but once you understand what this SUMIF is doing then everything else just falls into place.
Well, hey! I wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
