Today, in Episode #1511, Bill reviews Counting Between Ranges using a Pivot Table solution.
Due to a slight mix up, the follow-up to Learn Excel from MrExcel Podcast #1508 is airing today - an episode nearly slated for the 'Lost Episode' vault!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Due to a slight mix up, the follow-up to Learn Excel from MrExcel Podcast #1508 is airing today - an episode nearly slated for the 'Lost Episode' vault!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1509: count between all ranges.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jellen.
Yesterday we had podcast 1508, the question of how to come up with the count of all of these values that fell between 10 and 20.
And I started to think about that; I think really when you get right down to it, he's probably trying to come up with the count for all of the ranges; he just simplified it down to this.
You know, “Hey, tell me how to get between 10 and 20”.
And then he would expand it to go between 20 and 30, 30 and 40, and 40 and 50, and so on.
So, if that's really what we're trying to do, a faster way to go is to use Insert, PivotTable.
Now I'm just going to create this pivot table on a new worksheet, that's fine.
And, you see, we just have one field here; so I'm going to take the Value field down to Row Labels.
I'm also going to take the Value field down to Values.
So, right now it says that, “Hey, there was a record of 1.43 and it showed up-- you know, once for a total 1.43.
A couple things we want to do: we want to choose this field and then Field Settings, and change to Count, so we can see how many times each record appeared.
And, of course that is not fascinating at all, each record probably-- each value appeared once.
But here's where it's really going to come into play: I will go up here at Group Field and I'm going to start at, let's say 10, and go to 50, in groups of 10, and click OK.
And we get as-- we get one record with less than 10; 10-20; 20-30, 30-40; 40-50; and a Grand Total.
The word Row Labels, I think it looks bad.
I'm going to click on the Design tab up here; Report Layout; go back to Tabular Forms; then I get Value.
If you don't like Count of Value, come back to the Options tab and you can just call it Count, up here.
And, good to go.
So, there you have it: a faster way to get all of the ranges rather than just one.
Don't have to worry about any formulas or anything like that.
Oh hey, I want to thank you for stopping by.
See you next time for other netcast of MrExcel.
Learn Excel from MrExcel podcast, episode 1509: count between all ranges.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jellen.
Yesterday we had podcast 1508, the question of how to come up with the count of all of these values that fell between 10 and 20.
And I started to think about that; I think really when you get right down to it, he's probably trying to come up with the count for all of the ranges; he just simplified it down to this.
You know, “Hey, tell me how to get between 10 and 20”.
And then he would expand it to go between 20 and 30, 30 and 40, and 40 and 50, and so on.
So, if that's really what we're trying to do, a faster way to go is to use Insert, PivotTable.
Now I'm just going to create this pivot table on a new worksheet, that's fine.
And, you see, we just have one field here; so I'm going to take the Value field down to Row Labels.
I'm also going to take the Value field down to Values.
So, right now it says that, “Hey, there was a record of 1.43 and it showed up-- you know, once for a total 1.43.
A couple things we want to do: we want to choose this field and then Field Settings, and change to Count, so we can see how many times each record appeared.
And, of course that is not fascinating at all, each record probably-- each value appeared once.
But here's where it's really going to come into play: I will go up here at Group Field and I'm going to start at, let's say 10, and go to 50, in groups of 10, and click OK.
And we get as-- we get one record with less than 10; 10-20; 20-30, 30-40; 40-50; and a Grand Total.
The word Row Labels, I think it looks bad.
I'm going to click on the Design tab up here; Report Layout; go back to Tabular Forms; then I get Value.
If you don't like Count of Value, come back to the Options tab and you can just call it Count, up here.
And, good to go.
So, there you have it: a faster way to get all of the ranges rather than just one.
Don't have to worry about any formulas or anything like that.
Oh hey, I want to thank you for stopping by.
See you next time for other netcast of MrExcel.
