Update: Microsoft fixed this problem in version 1804. See:
Why does the revenue field in your pivot table always Count instead of Sum? It is one of two reasons. Both reasons, along with workarounds, are found in this episode. Recap:
In a perfect world, numeric fields will Sum in a pivot table
Why do they sometimes Count?
Reason 1: Empty or text cells
How to Fix reason 1: Go To Special Blanks. 0. Ctrl+Enter
Reason 2: You are selecting the whole column
You are selecting the whole column so you can add more data later
Ctrl+T to the rescue
Notice the end of table marker
Why does the revenue field in your pivot table always Count instead of Sum? It is one of two reasons. Both reasons, along with workarounds, are found in this episode. Recap:
In a perfect world, numeric fields will Sum in a pivot table
Why do they sometimes Count?
Reason 1: Empty or text cells
How to Fix reason 1: Go To Special Blanks. 0. Ctrl+Enter
Reason 2: You are selecting the whole column
You are selecting the whole column so you can add more data later
Ctrl+T to the rescue
Notice the end of table marker
Transcript of the video:
Learn Excel for MrExcel Podcast, Episode 2001 – Pivot Table Why Count Hey I'll be podcasting this whole book.
Go click that "i" on the top right hand corner to see to the playlist.
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Alright so it happens every single time.
I'm in front of a room full of accountants.
I'm doing my Power Excel Seminar and I start to talk about Pivot Tables and I do insert Pivot Table, okay.
Put any field down the left hand side and then I check mark Revenue and mine gets a SUM and goes to the right place and someone will invariably raise their hand and say, whoa, wait a second, that never works for me, I always get Account.
Why am I getting account?
As they look, it comes down to one of two reasons.
There's always one or two reasons.
Reason number one: you have some empty sales.
There's some data that you don't have.
There's either text there or empty cells and we create a Pivot Table from this Product and Revenue.
First off, the Revenue goes to the wrong place.
If you would drag it to the right place you get a count, right?
instead of being 6.7 million dollars, you're a 559 Dollar company, right?
So the solution, if that's your problem, and in most cases is not, is to select al the cells that should be numeric, like this and then Home, Find and Select, Go To Special, choose the blanks or if your problem is Text, choose the Text.
Click OK, now all the blanks are selected 0 + CTRL ENTER to fill those in.
Now insert Pivot Table okay.
Check mark Product, check mark Revenue and everything will work perfectly.
Alright, but here's the reason why most people run into this problem, is they've been taught somewhere along the way,and I appreciate the person that taught this to you, to not select one Solid Data but to choose all of the Data.
Alright and they're doing that because they know that you're going to be adding more data next month, or next year, or something like that, and they don't want you to go through the horribly, painful, awful, terrible process of clicking Change Data Source and specifying the extra rows.
I know that's like one, two, three, six clicks of hell.
Alright, so because of that, you're getting COUNTS instead of SUMS.
Let's stop doing that and do this: first, let's take a Pivot Table, create the Pivot Table, just from a single cell.
Insert Pivot Table, okay.
Put whatever you need to put in the Pivot Table, Products and Revenue.
I mean we're going to add the date field in here, across the top and you see very nicely Excel 2016 has summarized that down the years for me.
I'm going to leave that there.
Okay, so now, you have your Pivot Table, everything is great.
You now want to add more data.
At this point, we do CTRL T, which is format as a table.
It applies some formatting.
If you hate the formatting, you just go back to light and everything is good.
I'm going to leave the formatting there though and I want to show you at the very end of the data.
At the end of the table, is this beautiful little angle icon, that is called the End Of Table Marker, End Of Table Marker.
So now that Pivot Table knows that is all the data down to the End Of Table Marker, we get some more data.
Oh, here's some more data for next year.
Take that data, copy the data CONTROL C, CONTROL Page Up to go to the previous sheet and then watch the End Of Table Marker, when I paste, is going to move.
It now becomes there, alright?
So now, anything that was based on this table, now is pointed in more cells.
Has the Pivot Table changed?
No, not yet, but if we refresh, just simply do a refresh, don't have to go in and do the six clicks under to Change Data Source we get the extra data appearing.
we went from 6.7 million up to 6.9 million.
Alright so, the title here is why Pivot Count and the reason is because of empty cells, which we can be fixed with Go To Special, Blank, Zero, CONTROL ENTER, but more likely it's because you're selecting the whole columns, when you originally created the Pivot Table.
By using Tables, CTRL T, it solves the problem.
Hey I want to encourage you to go out and buy this book it has all of these tips.
Will be a great cross reference this whole series of 60 podcastS in August and September.
Recap, in a perfect world numeric fields will SUM in a Pivot Table.
Why do they sometimes count?
It's either empty or text cells, which you do with Go To Special, Blank, Zero, CONTROL ENTER or you're selecting the whole column and you're doing that so you can have more data later.
CTRL T to the rescue.
That End Of Table Marker shows you where the end of data is.
Go click that "i" on the top right hand corner to see to the playlist.
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Alright so it happens every single time.
I'm in front of a room full of accountants.
I'm doing my Power Excel Seminar and I start to talk about Pivot Tables and I do insert Pivot Table, okay.
Put any field down the left hand side and then I check mark Revenue and mine gets a SUM and goes to the right place and someone will invariably raise their hand and say, whoa, wait a second, that never works for me, I always get Account.
Why am I getting account?
As they look, it comes down to one of two reasons.
There's always one or two reasons.
Reason number one: you have some empty sales.
There's some data that you don't have.
There's either text there or empty cells and we create a Pivot Table from this Product and Revenue.
First off, the Revenue goes to the wrong place.
If you would drag it to the right place you get a count, right?
instead of being 6.7 million dollars, you're a 559 Dollar company, right?
So the solution, if that's your problem, and in most cases is not, is to select al the cells that should be numeric, like this and then Home, Find and Select, Go To Special, choose the blanks or if your problem is Text, choose the Text.
Click OK, now all the blanks are selected 0 + CTRL ENTER to fill those in.
Now insert Pivot Table okay.
Check mark Product, check mark Revenue and everything will work perfectly.
Alright, but here's the reason why most people run into this problem, is they've been taught somewhere along the way,and I appreciate the person that taught this to you, to not select one Solid Data but to choose all of the Data.
Alright and they're doing that because they know that you're going to be adding more data next month, or next year, or something like that, and they don't want you to go through the horribly, painful, awful, terrible process of clicking Change Data Source and specifying the extra rows.
I know that's like one, two, three, six clicks of hell.
Alright, so because of that, you're getting COUNTS instead of SUMS.
Let's stop doing that and do this: first, let's take a Pivot Table, create the Pivot Table, just from a single cell.
Insert Pivot Table, okay.
Put whatever you need to put in the Pivot Table, Products and Revenue.
I mean we're going to add the date field in here, across the top and you see very nicely Excel 2016 has summarized that down the years for me.
I'm going to leave that there.
Okay, so now, you have your Pivot Table, everything is great.
You now want to add more data.
At this point, we do CTRL T, which is format as a table.
It applies some formatting.
If you hate the formatting, you just go back to light and everything is good.
I'm going to leave the formatting there though and I want to show you at the very end of the data.
At the end of the table, is this beautiful little angle icon, that is called the End Of Table Marker, End Of Table Marker.
So now that Pivot Table knows that is all the data down to the End Of Table Marker, we get some more data.
Oh, here's some more data for next year.
Take that data, copy the data CONTROL C, CONTROL Page Up to go to the previous sheet and then watch the End Of Table Marker, when I paste, is going to move.
It now becomes there, alright?
So now, anything that was based on this table, now is pointed in more cells.
Has the Pivot Table changed?
No, not yet, but if we refresh, just simply do a refresh, don't have to go in and do the six clicks under to Change Data Source we get the extra data appearing.
we went from 6.7 million up to 6.9 million.
Alright so, the title here is why Pivot Count and the reason is because of empty cells, which we can be fixed with Go To Special, Blank, Zero, CONTROL ENTER, but more likely it's because you're selecting the whole columns, when you originally created the Pivot Table.
By using Tables, CTRL T, it solves the problem.
Hey I want to encourage you to go out and buy this book it has all of these tips.
Will be a great cross reference this whole series of 60 podcastS in August and September.
Recap, in a perfect world numeric fields will SUM in a Pivot Table.
Why do they sometimes count?
It's either empty or text cells, which you do with Go To Special, Blank, Zero, CONTROL ENTER or you're selecting the whole column and you're doing that so you can have more data later.
CTRL T to the rescue.
That End Of Table Marker shows you where the end of data is.
