+ Reply to Thread
Results 1 to 10 of 10

Pivot Table not counting Zeros

  1. #1
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    114

    Pivot Table not counting Zeros

    Hi There!

    I have a database with monthly calculations. Inside are with value and some are zeros. I need to count only those that has values >0 in the Pivot table but it is counting also the zeros because it has the formula in it.

    Is there anyway to count in Pivot not including the zero?

    Attached is the excel table.

    Thanks for your help
    Ricky
    Attached Files Attached Files
    Last edited by rickyilas; 06-24-2022 at 03:21 AM.

  2. #2
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    Microsoft Office 365
    Posts
    1,230

    Re: Pivot Table not counting Zeros

    I am not sure if you really can.
    You could do something like what i attached.
    Utilize a Unique function and then a filter function.

    =UNIQUE(Table1[Project],FALSE)
    =COUNT(FILTER(Table1[May-22],($A16=Table1[[Project]:[Project]])*(Table1[May-22]<>0)))


    Its not great but it could work. Transformed your data into a table as well.
    Attached Files Attached Files
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  3. #3
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    114

    Re: Pivot Table not counting Zeros

    Thanks Dosydos for your reply. Appreciate it.

    But i would prefer not using any formula and just getting the result in the pivot table itself.
    I found some instructions in the net but couldn't do it. I don't know if i am missing something in the instructions:

    Drag the first column to the row labels.
    Drag the second column to the Data Area twice.
    Right click on one of the data area columns and under Summarise data by, select Count.
    Click on the filter drop down under row labels and under Value Filters, select Greater than.
    Enter 0 there.
    Click on OK.
    (I don't understand the column he means.)

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,739

    Re: Pivot Table not counting Zeros

    This is due to the fact of your formatting of cells as they now contain an hyphen.
    If you remove this formatting to make them blank cells, your count is correct

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,744

    Re: Pivot Table not counting Zeros

    Hi,

    So you're going to add each and every month to the Values area of the Pivot Table? Doesn't sound very flexible, nor the most pragmatic approach.

    I would prefer to have the months in the Columns area, which I would do by first unpivoting those columns using Power Query. I would then add the resulting table to the Data Model, so that a measure can be created to give you your desired totals.

    See the attached.

    Regards
    Attached Files Attached Files
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    114

    Re: Pivot Table not counting Zeros

    Thanks Pepe for your reply. I tried making a formula in my original data that if it is 0, it should be "" blank, but even this ""blank is being counted. The attached file has no formula but just hypen resulted from copying as values.

  7. #7
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    114

    Re: Pivot Table not counting Zeros

    Hello Xor,

    Thanks for your file and it is exactly the result I wanted. Unfortunately, I don't know how to use power Query and don't understand how you did the pivot. I was looking for the Data source but couldn't find it, maybe because it is Power Query. i would have loved to learned how to do that. Any reference?

    Thanks

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,744

    Re: Pivot Table not counting Zeros

    You can look at the transformations I did in Power Query via the Data ribbon (Queries & Connections) and then double-clicking on the query in the pane on the right.

    That transformed table is then used as the source for the Power Pivot table, which you can see by opening up the Data Model (Data ribbon/Manage Data Model).

    Regards

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,739

    Re: Pivot Table not counting Zeros

    Quote Originally Posted by rickyilas View Post
    Thanks Pepe for your reply. I tried making a formula in my original data that if it is 0, it should be "" blank, but even this ""blank is being counted. The attached file has no formula but just hypen resulted from copying as values.
    As I suggested, you need to use BLANK cells; The "" is a text string ( although it is an empty one) which then will be counted in the PT
    Of course if you copy/pasted from somewhere else it's a different story
    Last edited by Pepe Le Mokko; 06-24-2022 at 02:24 AM.

  10. #10
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    114

    Re: Pivot Table not counting Zeros

    Thanks all for your reply. I will try to explore Power Query, i think it's the solution I can use to achieve this problem.

    Cheers!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pivot table won't exclude Zeros
    By tuph in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 09-14-2015, 10:23 PM
  2. Pivot Table: Zeros to Dashes
    By GreatLakesJK in forum Excel General
    Replies: 7
    Last Post: 01-28-2011, 04:16 PM
  3. Remove zeros from a Pivot table
    By Ozwilly in forum Excel General
    Replies: 1
    Last Post: 09-09-2010, 11:46 PM
  4. Suppress zeros in a pivot table
    By dcgrove in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-13-2009, 05:15 PM
  5. Data in my pivot table changes to zeros
    By MyrtlePeacock in forum Excel General
    Replies: 3
    Last Post: 01-05-2006, 10:30 AM
  6. Suppress Zeros in a Pivot Table
    By Simon Shaw in forum Excel General
    Replies: 2
    Last Post: 06-27-2005, 05:05 PM
  7. Pivot Table and Zeros
    By Celeda in forum Excel General
    Replies: 0
    Last Post: 01-01-2005, 04:58 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1