For decades, some pivot tables give you a Count instead of a Sum. The problem was usually caused by a few empty cells in your data. Starting in version 1804 of Office 365, the problem is fixed.
To read more about this: Improved Handling of Empty Cells in Pivot Table Source
Excellers have been annoyed by Count of Revenue in pivot tables for years
It happens when you have one or more empty cells in the revenue column
Starting with version 1804 of Excel in Office 365, the behavior is fixed.
You will still get a count if someone puts spaces instead of a number.
To read more about this: Improved Handling of Empty Cells in Pivot Table Source
Excellers have been annoyed by Count of Revenue in pivot tables for years
It happens when you have one or more empty cells in the revenue column
Starting with version 1804 of Excel in Office 365, the behavior is fixed.
You will still get a count if someone puts spaces instead of a number.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2195.
Why Does My Pivot Table Count Instead Of Sum.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Every time, I'm doing what on my live seminars.
And I create a pivot table, insert pivot table and I check mark list say, customer, and revenue.
Wait a second, how do you get sum of revenue there?
I always get a count and when I hear that, I know it's one of two things.
It's either, That they have a couple of blank cells in revenue column or they are choosing instead of one cell in the pivot table, all the cells like this.
And I get it, I know that by doing this.
They can add more data, later.
But you can do that.
adding more data, later just by formatting this with format as table or [ ctrl T ] Alright! So now, by adding this two blank cells in, or even one blank cell in.
When I create a pivot table, insert, pivot table.
[ OK ] And I choose let's say, customer.
If i check mark revenue, what use to happen is revenue would fly here.
But people have learned you can just check mark revenue.
So, what they do, they take revenue and drag it to the values area and it becomes a count instead of a sum.
Alright! This is been broken for 30 years.
But the last few times, I did this, I was really shock to see that it is showing up as the sum, instead of a count and so let's look at this File, Account.
I'm of course, in office 365 and I'm in version 1805.
Now, my laptop which is on version 1804 has also been fixed.
Now, I went back to Ash, you remember Ash, the Wednesday tip was from Ash.
And I said, hey! Ash, what's the story with this?
This is a great improvement, this is working now.
She said, "Yes, someone reported it's a bug." and there's is a mix of numbers and blank cells, that is getting interpreted as text.
So, we change that now into interpret as numbers.
Which is awesome.
It also works, if you choose all the columns like this, which of course is now giving it 400, 500, 900 numbers, two blank cells.
And then a mini blank cell.
you know, where the mini blank cell, it will still correctly do the right thing.
So, customer and revenue.
Now, we get a sum of revenue instead of a count revenue.
That's a beautiful thing.
Now, the thing it will do.
I ran into the people, who instead of clearing cells by pressing the [ delete ] key, like I just did.
They'll clear cells by typing [ space ], [ space ], [ space ], [ space ].
Whichever now, that is text and that will cause us to get a count, instead of sum.
So, insert, pivot table, [ OK ] I'll choose customer, I'll choose revenue.
See revenue goes the wrong spot, I drag it to the right spot and I'm getting a count, instead of sum.
So, as we have an empty cell, not [ space ], [ space ], [ space ], [ space ].
But overall a great improvement.
Tips like this, are in my book MrExcel Live, the 54 greatest tips of all time Alright! Episode recap.
We have been annoyed by count of revenue in pivot tables for years.
It happens when you have one or more empty cells in a revenue column.
Starting with version 1804 of office 365, the behavior is fixed.
See if it's mix of numbers and empty cells, it will treat as number.
You still gonna get it count, when someone puts spaces instead of a number.
Hey! Wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Why Does My Pivot Table Count Instead Of Sum.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Every time, I'm doing what on my live seminars.
And I create a pivot table, insert pivot table and I check mark list say, customer, and revenue.
Wait a second, how do you get sum of revenue there?
I always get a count and when I hear that, I know it's one of two things.
It's either, That they have a couple of blank cells in revenue column or they are choosing instead of one cell in the pivot table, all the cells like this.
And I get it, I know that by doing this.
They can add more data, later.
But you can do that.
adding more data, later just by formatting this with format as table or [ ctrl T ] Alright! So now, by adding this two blank cells in, or even one blank cell in.
When I create a pivot table, insert, pivot table.
[ OK ] And I choose let's say, customer.
If i check mark revenue, what use to happen is revenue would fly here.
But people have learned you can just check mark revenue.
So, what they do, they take revenue and drag it to the values area and it becomes a count instead of a sum.
Alright! This is been broken for 30 years.
But the last few times, I did this, I was really shock to see that it is showing up as the sum, instead of a count and so let's look at this File, Account.
I'm of course, in office 365 and I'm in version 1805.
Now, my laptop which is on version 1804 has also been fixed.
Now, I went back to Ash, you remember Ash, the Wednesday tip was from Ash.
And I said, hey! Ash, what's the story with this?
This is a great improvement, this is working now.
She said, "Yes, someone reported it's a bug." and there's is a mix of numbers and blank cells, that is getting interpreted as text.
So, we change that now into interpret as numbers.
Which is awesome.
It also works, if you choose all the columns like this, which of course is now giving it 400, 500, 900 numbers, two blank cells.
And then a mini blank cell.
you know, where the mini blank cell, it will still correctly do the right thing.
So, customer and revenue.
Now, we get a sum of revenue instead of a count revenue.
That's a beautiful thing.
Now, the thing it will do.
I ran into the people, who instead of clearing cells by pressing the [ delete ] key, like I just did.
They'll clear cells by typing [ space ], [ space ], [ space ], [ space ].
Whichever now, that is text and that will cause us to get a count, instead of sum.
So, insert, pivot table, [ OK ] I'll choose customer, I'll choose revenue.
See revenue goes the wrong spot, I drag it to the right spot and I'm getting a count, instead of sum.
So, as we have an empty cell, not [ space ], [ space ], [ space ], [ space ].
But overall a great improvement.
Tips like this, are in my book MrExcel Live, the 54 greatest tips of all time Alright! Episode recap.
We have been annoyed by count of revenue in pivot tables for years.
It happens when you have one or more empty cells in a revenue column.
Starting with version 1804 of office 365, the behavior is fixed.
See if it's mix of numbers and empty cells, it will treat as number.
You still gonna get it count, when someone puts spaces instead of a number.
Hey! Wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
