Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Pivot Table - Error message on summarising the Count field into Sum field

  1. #1

    Pivot Table - Error message on summarising the Count field into Sum field



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Please can someone help me with this query?
    I am trying to create a pivot with a dataset 753 rows and 17 columns.
    The problem I am encountering is when I tried to change the “Count” field to a “Sum” for the YTD Mar15 Value.
    The below error prompt appears "We cant summarize this field with Sum because it' not a supported calculation for Text data types".
    I have tried to change the format of the data to Text, General, Number and Custom but with no success. I even deleted all of the unsupported characters in case these were the problem.
    So what is it that I am doing wrong? Can someone help me?
    Many thanks,
    My-Linh

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    Maybe there are spaces in the numbers, at the end or start. Try doing a Data>Text To Columns on them.

  3. #3
    Thanks, Bob. This is not the issue. I've checked. There are no spaces in front or behind. I'have also tried to copy the selcted cells to a new workbook altogether but still no success.

    Any more ideas as to what could be wrong?

    Thank you.


  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    Could we see the workbook?

  5. #5
    Yes. How do I upload the file?

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    Click the Advanced button in the reply, there is a Manager button there.

  7. #7
    Here is the attached file, Bob.
    Please let me know if you can solve this issue.
    Many thanks.
    Attached Files Attached Files

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    I notice that you have the data in Power Pivot, was the pivot from this? I notice all the number columns are text, and I couldn't change them. The original data in the spreadsheet is not a table, how did you load it into Power Pivot?

  9. #9
    Excel 2013 Pro Plus
    Got it to work by filling in the 5 no-data rows (blanks) with random numbers.
    Also created a Table from the raw data and loaded it into the model. No external source now.
    Also used explicit instead of implicit Calculated Fields.
    A summary of a summary is usually not a good idea.
    http://www.mediafire.com/view/73dyxl.../04_29_15.xlsx

  10. #10
    Hi Bob,
    I have sorted it out now by simply selecting the data again and click on "recommended pivot tables" rather than "pivot table". This seems to have done the job and I could drag all the other fields in. For some reasons the recommended pivot table selection understood what I needed to do with the data and the other selection didn't seem to work because my field headings might be too similar in description.
    Thanks again for looking into this issue for me and so prompt in reply.
    My-linh

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •