Results 1 to 7 of 7

Thread: PivotTable sub-totals with zero cells

  1. #1

    PivotTable sub-totals with zero cells



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

    My sub-totals are not adding columns that have empty cells or error values formatted to display 0. How do I format the sub-totals to treat these cells as 0?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    Are you getting a count instead or an error?


  3. #3
    I'm getting a SUM but the value displays as "-".

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    Is it possible to see what you mean with a sample attachment?


  5. #5
    This is a small portion of the file. I can't attach the entire file, but if you need to see the formatting I could try to duplicate in a smaller version. As you can see below, the sub-total of L1 does not appear because (I think) there are empty cells in the column. I can't remove the rows because there are values in other columns. The sub-total of L2 sums the rows in the category correctly.


    L1 28,522 28,734
    L1 - -
    L1 - -
    L1 21,001 23,755
    Sub - -
    L2 73,127 54,012
    L2 57,854 49,510
    Sub 130,981 103,522

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    If your numbers in the table are formatted as numbers, then not sure without seeing an actual sample file.


  7. #7
    The table was formatted for numbers however, as I was preparing a sample to attach I discovered the problem which was primarily related to duplicates.

Posting Permissions

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