SUM function in Pivot Table Not Working
I have a pivot table on one sheet that is coming from a Microsoft Query MySQL datafeed on another one of my sheets.
Consider the information from the datafeed to be like so:
date | order | SKU | Quantity
-----------------------------------
5/1/14 123456 11111 1
5/1/14 234567 22222 1
5/1/14 456789 33333 2
5/2/14 987654 44444 1
5/2/14 876543 55555 3
When I make a pivot table for this information, I use the date for the row labels. I then want to count the amount of SKUs for that day, and add the quantity of SKUs for that day. So I drag the SKU column into the values section and make sure that COUNT is selected. I then drag the Quantity column into the value section, and when I select SUM, my values wind up being zero. See below for what is happening:
Row Labels | Count of SKUs | Sum of Quantity
------------------------------------------------
5/1/14 3 0
5/2/14 2 0
The Sum of Quantity column should not be zeros, it should be 4 for 5/1 and 4 for 5/2. I have never encountered this problem before, and I am assuming it has something to do with the datafeed being linked to a MySQL query.
I have tried to change the numbers in the Quantity column to number format with no luck. I have absolutely no idea what is causing this, and I'm assuming it's probably something simple that I am overlooking. But I was hoping someone else has encountered this problem and/or has a solution to this.
Help please!
Thanks in advance!
-Anthony
SOLUTION (below):
The data type of the Quantity column in the MySQL database table was VARCHAR. I changed the data type to INT and refreshed the datafeed, and now the pivot table works fine.
Answer
I am guessing that your query returns these numbers as text. If you input three 1's in cells preceded by an apostrophe to signal Excel that you want them to be considered strings and then use SUM() over that range it will yield zero. Consider converting your column to numbers.