Why does the automatic subtotals command sometimes choose to Sum and sometimes choose to Count? Episode 514 shows you why Excel seems to arbitrarily count or sum.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Okay! Welcome back to the MrExcel podcast.
I'm Bill Jelen.
Just checking in here our podcast 500 giveaway, still have about 15 or 20 prizes left to go.
So, if you entered don't give up yet, we're giving those away slowly.
Although, now we're doing five a day.
It shouldn't take long until we have everything out to everyone.
Thanks for the people that entered.
Our question today comes from someone that was at one of my power Excel seminars.
I showed the automatic subtotals feature, and I said well wait a second, when I use this at work.
It's an intermittent problem, sometimes it decides to sum and sometimes it decides to count.
Why does Excel, why can't Excel make up it's mind?
And let me show you exactly how this works.
We have a data set here, region, product, date, customer.
It's sorted by customer, quantity, revenue, cost of goods, sold and profit and if I use data subtotals, when you look at the subtotal dialog box it says we're going to subtotal by the leftmost column.
That's always what it wants to do and you of course change it to whatever you need to do.
So for example, the customer column and then it says it's going to use Sum function.
We'll talk about that in a minute and always add it by default to the rightmost column.
So, in this case that's profit and of course, you would usually go through and add a check mark for the other boxes, that you want to subtotal.
So, that all works out fine and everything is good, and it does the sum exactly like you wanted to do.
I'm going to go to a different data set here though and there's one critical difference with the other data set in this data set.
The Rightmost column is a sales rep name.
So, instead of having numbers in the rightmost column, it has text.
Once Excel sees text in that right most column, it doesn't know how to sum the text.
So, if we go to data subtotals, again it still wants to subtotal by the leftmost column, so we can change customer.
But now because the rightmost column rep is checked makes else as well.
There's no way that I can total those rep names.
So, therefore I'm going to use a count.
So, you really just have to be aware of this and look at your data set before use subtotals.
If you see that your right most column is text-based, you're going to have to go in and pay particular attention to the used function in drop down.
In order to change it from count to sum.
There we go.
I hope that helps and thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Just checking in here our podcast 500 giveaway, still have about 15 or 20 prizes left to go.
So, if you entered don't give up yet, we're giving those away slowly.
Although, now we're doing five a day.
It shouldn't take long until we have everything out to everyone.
Thanks for the people that entered.
Our question today comes from someone that was at one of my power Excel seminars.
I showed the automatic subtotals feature, and I said well wait a second, when I use this at work.
It's an intermittent problem, sometimes it decides to sum and sometimes it decides to count.
Why does Excel, why can't Excel make up it's mind?
And let me show you exactly how this works.
We have a data set here, region, product, date, customer.
It's sorted by customer, quantity, revenue, cost of goods, sold and profit and if I use data subtotals, when you look at the subtotal dialog box it says we're going to subtotal by the leftmost column.
That's always what it wants to do and you of course change it to whatever you need to do.
So for example, the customer column and then it says it's going to use Sum function.
We'll talk about that in a minute and always add it by default to the rightmost column.
So, in this case that's profit and of course, you would usually go through and add a check mark for the other boxes, that you want to subtotal.
So, that all works out fine and everything is good, and it does the sum exactly like you wanted to do.
I'm going to go to a different data set here though and there's one critical difference with the other data set in this data set.
The Rightmost column is a sales rep name.
So, instead of having numbers in the rightmost column, it has text.
Once Excel sees text in that right most column, it doesn't know how to sum the text.
So, if we go to data subtotals, again it still wants to subtotal by the leftmost column, so we can change customer.
But now because the rightmost column rep is checked makes else as well.
There's no way that I can total those rep names.
So, therefore I'm going to use a count.
So, you really just have to be aware of this and look at your data set before use subtotals.
If you see that your right most column is text-based, you're going to have to go in and pay particular attention to the used function in drop down.
In order to change it from count to sum.
There we go.
I hope that helps and thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
