Count vs Sum in pivot tables

[cthompson]

Hi all (again)

One last question on pivot tables. Why when adding a column label to a pivot table it will default to a sum of the data and other times a count of the data? I have noted these differences when one set of data is text and the other is numeric and that makes sense. However there are times when both sets of data are numeric but the pivot table will count one list and sum the other. I sorry this is one of the other annoying things that drive us all crazy.

Chris

 

[XOR LX]

Hi,

Interesting. I've only ever seen it default to 'Count of' if at least one value in that field is not numeric. Perhaps you can post a small, mocked-up column or two for which the associated Pivot Table exhibits the behaviour you outline?

Regards

 

[RoryA]

However there are times when both sets of data are numeric but the pivot table will count one list and sum the other.

I'd assume you have one or more blanks in your first dataset - that would cause a default of Count.

 

[cthompson]

I think I have identified the problem.

I created 2 pivot tables the top one was initially had in both set 1 and set 2 data either blanks or a number with a label prefix and the pivot table recognized both sets as text and used count in the summations. In the second pivot table i used numeric in both data sets and the pivot table recognized both as numbers and used the sum function.

What was interesting was when i replaced the data from the second table back to the first table no matter if i redefined "change data source" for the first pivot table and regardless of what kind of number was replaced both sets of data were recognized as a label and the count function was employed by the pivot table.

So it seems that the first time a pivot is created will define how the data will be treated unless you completely delete the pivot and redefine a new table from scratch. In most cases where I have noticed this problem is when I am re-organizing an existing pivot table and adding or modifying the underlying data.

Thanks again for the help, again one of the small annoying things when using the pivot table
Chris

Pivot table 1
Row Labels	Count of Set 1	Count of Set 2						Data 1
A	1						Lable	Set 1	Set 2
B	1	1					A	200	0
C	1	1					B	300	1
D	1	1					C	400	2
E	1	1					D	500	3
F		1					E	600	4
G	1	1					F	700	5
Grand Total	6	6					G	800	7
Pivot table 2
Row Labels	Sum of Set 1	Sum of Set 2						Data 2
AA	500	3
AB	600	4					Lable	Set 1	Set 2
AC	700	5					X	200	0
AD	800	7					y	300	1
X	200	0					Z	400	2
y	300	1					AA	500	3
Z	400	2					AB	600	4
Grand Total	3500	22					AC	700	5
							AD	800	7

<colgroup><col><col span="2"><col span="7"></colgroup><tbody>
</tbody>

 

[RoryA]

So it seems that the first time a pivot is created will define how the data will be treated unless you completely delete the pivot and redefine a new table from scratch. In most cases where I have noticed this problem is when I am re-organizing an existing pivot table and adding or modifying the underlying data.

All you have to do is change the function to Sum if that's what you need. The Count is merely a default, not a fixed option.

 

[cthompson]

Thanks again, and we do make that adjustment. My curiosity always gets the better of me as to why something works one way and not necessarily the other.
CT

 

[James006]

Hi,

To default to Sum instead of Count, You can have a look at :Default Your Pivot Tables To SUM not COUNT

HTH

 

[cthompson]

Much obliged thank you.
CT