1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Pivot table not summing negative numbers

Discussion in 'Business Applications' started by holymoly, Nov 14, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    I changed the formula in column a source data worksheet so that it returns some negative numbers.

    A pivot table using this data source, doesn't include these negative numbers in its sum total.

    Not sure why its not included in the sum total.

    If I highlight the entire column in the worksheet, the total does include these negative numbers.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    can you post a copy of the spreadsheet with dummy data - remember a public forum here -

    i assume the refresh has worked and it has seen some changes ?
    try adding some +ve numbers and see if the sum is still working and the range its using is still correct
    also check the filters you have - page/columns etc on the pivot table - just make sure the filters are not coincidently excluding the -ve numbers
     
  3. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    Unfortunately, file too big, and too many conditional formulae requiring many columns.

    No filters on in data source and formatting of column is consistent.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    do not normally do this - is it possible to email ? and would you be prepared to do that or is the info confidential - if so you can use the email address here - or i will PM you
     
  5. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    can you PM, your email address. Need to really cut down the spreadsheet file too, uses ODBC tables and original size is 24MG
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    i have v2003 - so could not modify your spreadsheet - so I recreated the pivot table on a newsheet and it worked for the correct totals on all
    so I added the transtype and selected SO - it than gave your answer

    is this the problem - you have not selected all the transtypes to include the full data set
     
  7. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    Hi etaf, not sure what you mean. The formula in YR order must have a SO condition?
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    Ok - If it has SO - then you are getting the correct result on the pivot table

    the transtype are as follows
    ETAF
    SO 205086.76
    CR -14530.36
    QU 1665.16
    RM 0

    Tot 192221.56

    see email
    Cells P & Q in your sales data sheet for the calcs for each transtype

    which if on the Pivot table - Page function dropdown - should be the same on the pivot table
     
  9. holymoly

    holymoly Thread Starter

    Joined:
    Jun 21, 2003
    Messages:
    635
    Now what you mean now. The transtype on pivot table was filtered.

    Missed that.

    Thanks for your help and have a great day.
     
  10. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    your welcome (y) thanks for letting us know
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1076699

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice