The reason that people create data like in podcast 826 is so that they can get totals by section to carry through to another report. However, if that other report used SUMIF, the problem would be solved. Episode 827 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I’m Bill Jelen.
In Yesterday’s netcast, we had this very ugly data set, and the reason that someone created that data set was that they needed to add totals in for each count, when, in fact, we really don’t have to do that-- we can do the SUMIF formula to do that.
I’ll show you how.
We have our data set here and I want to create, basically, one line per account-- totals per account.
I’m going to copy my account heading over, and we’ll go to the Advanced now-- in Excel 2003, this was Data, Filter, Advanced Filter-- we’re going to Copy the list to another location, we’re going to copy it to our heading that says Account, and I want “Unique records only”.
What that’s going to do for me, is just give me each account number once.
So where I had hundreds of records on the left hand side, I only have just a few records on the right hand side.
I want to total up a couple of different columns-- FTE and Total.
So, =SUMIF --SUMIF says, “Hey, go look through everything over here in Column B, from B2 down to B227-- I hit the F4 key to lock that to make it absolute; and we say we want to see if it’s equal to this value that’s here in G; and I’m going to press F4 three times to lock just the G, but allow the row to change; and when we find a match, we’re going to add up the corresponding value from Column C. So, C2 to C227-- of course, I hit F4 there-- and it gives me 14.
Let’s just copy this over; we still get the same 14, I’m going to edit the formula to say that our totals are coming from E instead of C.
So we get a couple nice SUMIF functions there, now I can click the Fill handle-- select them both, double-click the Fill handle-- and what we’re going to get is Totals-- Total FTEs and Total Totals for each account.
This is a great little formula, we can add these formulas over on the other sheet that had links back to each individual Total.
So, a much more flexible way to go, and we don’t have to worry about keeping all those manual totals in the middle of our data.
We can now sort and pivot the left total, but we still have totals somewhere else that will grab the correct Totals for each count.
Great way to go.
I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
In Yesterday’s netcast, we had this very ugly data set, and the reason that someone created that data set was that they needed to add totals in for each count, when, in fact, we really don’t have to do that-- we can do the SUMIF formula to do that.
I’ll show you how.
We have our data set here and I want to create, basically, one line per account-- totals per account.
I’m going to copy my account heading over, and we’ll go to the Advanced now-- in Excel 2003, this was Data, Filter, Advanced Filter-- we’re going to Copy the list to another location, we’re going to copy it to our heading that says Account, and I want “Unique records only”.
What that’s going to do for me, is just give me each account number once.
So where I had hundreds of records on the left hand side, I only have just a few records on the right hand side.
I want to total up a couple of different columns-- FTE and Total.
So, =SUMIF --SUMIF says, “Hey, go look through everything over here in Column B, from B2 down to B227-- I hit the F4 key to lock that to make it absolute; and we say we want to see if it’s equal to this value that’s here in G; and I’m going to press F4 three times to lock just the G, but allow the row to change; and when we find a match, we’re going to add up the corresponding value from Column C. So, C2 to C227-- of course, I hit F4 there-- and it gives me 14.
Let’s just copy this over; we still get the same 14, I’m going to edit the formula to say that our totals are coming from E instead of C.
So we get a couple nice SUMIF functions there, now I can click the Fill handle-- select them both, double-click the Fill handle-- and what we’re going to get is Totals-- Total FTEs and Total Totals for each account.
This is a great little formula, we can add these formulas over on the other sheet that had links back to each individual Total.
So, a much more flexible way to go, and we don’t have to worry about keeping all those manual totals in the middle of our data.
We can now sort and pivot the left total, but we still have totals somewhere else that will grab the correct Totals for each count.
Great way to go.
I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
