SUM function in Pivot Table Not Working

Question

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.

we can't help without seeing the sql that does the pivot. – Hogan May 12 '14 at 16:19 — Hogan, May 12 '14 at 16:19

score 1 · Answer 1 · answered May 12 '14 at 16:21

1

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.

answered May 12 '14 at 16:21

ApplePie

8,814
5
39
60

Reading your response prompted me to check the data type in the MySQL database. The data type was VARCHAR. I changed it to INT, and now the SUM function in the pivot table works fine. Thanks. – aCarella May 12 '14 at 16:33

score 1 · Accepted Answer · answered May 12 '14 at 16:32

1

The solution was that the "quantity" column in the MySQL database was VARCHAR. Once the column was changed to INT and the datafeed was refreshed, the pivot table worked fine.

answered May 12 '14 at 16:32

aCarella

2,369
11
52
85

score 1 · Answer 3 · edited Jan 22 '15 at 20:50

1

Easy fix is :

Highlight your "quantity" column and do "text to columns" which is under "Data", click on "Fixed with" and separate just number do "finish".
This will delete the unnecessary spaces between your number.
Rerun the pivot table

edited Jan 22 '15 at 20:50

Patrice

4,641
9
33
43

answered Jan 22 '15 at 20:10

kranti

11
1

SUM function in Pivot Table Not Working

3 Answers3