pivot table Grand total not matching data

[SUPPO_USN]

I have a pivot table based on 8,360 Rows of data.

The pivot table has Report Filters set resulting in a total count of 2,823 lines (VALUES section of the Pivot returns SUM of a data column with either 1 or 0).

If I double click the grand total amount in the pivot table, it displays 3,918 lines of source data. (all of which have a value of 1 listed in the column being looked at in the VALUES section of the pivot table.)

When I filter the source data directly to match the filters on the pivot table, I get 3,918 lines of data.

If I set the VALUES section of the pivot to return a COUNT it displays the 3,918 total.

why would the SUM and COUNT calculations of the VALUES section be different if the returned data has all 1s in that specific column being looked at?

I hope I've explained my issue clearly. If not, let me know and I'll try to explain in more detail. Any insight is appreciated.

r/

SUPPO

[than_gold]

sum - adding the values
count - The number of nonempty values
both work different the way they deal with fields.
If you could post the sample sheet, it will be easy to help you.

[SUPPO_USN]

I'll attempt to clean the file and upload.

Each cell being counted/Summed has a value of 1, no blanks, so it should return the same number. If i double click the summed grand total (listed as 2,823) it pulls 3,918 rows all of which have a 1 in the summed column and when i SUM() the column it returns 3,918.

Just isn't making sense and would mean I've been reporting the wrong number/data for a while.

[than_gold]

with sample excel sheet, this can be helped further.

[xlnitwit]

Hi,

My best guess would be that some of the 1s are actually text and not numeric. Are they left aligned in the cells?