Ask a new question

Pivot table cannot summarize Numbers

I am quite an experienced Excel user, having created dozens of pivot tables. Office 2013 was recently installed on our machines, and since then I have been unable to create a pivot table with Summarized values.

The symptom is that by default the Pivot table sets the value to "Count". When I click on "Value field settings", and select "Sum", the following message appears:

"We can't summarize this field with Sum because its not a supported calculation for Text data types". 

I fully understand that Text data types cannot be Summed. 

But I have only numbers in the respective columns!

Even previously created (Office 2010) excel charts with pivot tables show the same symptom in 2013.

Any idea how to solve it? Or is it a bug?

Please see a link to the file:
https://skydrive.live.com/redir?resid=6A9AB5AF95EB90BA!110&authkey=!AAFfojmIkiydSew
Answer
Answer

Hi,

 

The Pivot Table you created was not via Insert > Pivot Table but was a Power Pivot instead.  The PowerPivot was recognizing the Sales Value column as a column of text values.  I simply multiplied all numbers by 1 (via Paste Special > Multiply).  This too did not solve the problem.  I then scrolled down the list to find one culprit cell (which was let aligned).  I corrected for that and refreshed your Pivot Table.  I then saw SUM instead of COUNT.

 

As Bernard mentioned, if you create a simple Pivot, you will get the SUM values.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

88 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

Sorry I overlooked the link on you message

I downloaded your workbook and agree with your problem - there was no way to summarize by SUM since Excel thought the field was text.

 

However, I use Insert | Pivot Table and made a second PT with no problem. Picture below.

See https://skydrive.live.com/#!/edit.aspx?cid=162119FF97286948&resid=162119FF97286948%213076&app=Excel

 

Please try making the PT again from the same data. If that fails we might need to go over the steps to make a PT

best wishes

 

 

http://people.stfx.ca/bliengme
A Guide to MS Excel 2013 for Scientists and Engineers

32 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated May 15, 2024 Views 147,086 Applies to: