PIVOT TABLES: Show correct count value based on different cell values
Results 1 to 4 of 4

Thread: PIVOT TABLES: Show correct count value based on different cell values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2014
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default PIVOT TABLES: Show correct count value based on different cell values

    Hello,

    I have a pivot table that currently shows the following:

    Column Labels
    EXPIRED COMING UP TO EXPIRATION
    Row Labels Count of Item Code Sum of Total Value Count of Item Code Sum of Total Value
    Tables 74 $817,008 27 $68,000
    Chairs 3 $422 19 $900,000
    Books 18 $254,000 20 $143,291


    However, the issue I am having is that I don't want the "COUNT OF ITEM CODES" to include a count where the field in data source, "Quantity on Hand" = 0. While the SUM of Total Value would not be affected by this filter, I would want COUNT OF ITEM CODE to reflect correctly...see below:


    Column Labels
    EXPIRED COMING UP TO EXPIRATION
    Row Labels Count of Item Code Sum of Total Value Count of Item Code Sum of Total Value
    Tables 50 $817,008 27 $68,000
    Chairs 1 $422 16 $900,000
    Books 17 $254,000 20 $143,291

    Any help is greatly appreciated!

    Thanks,
    Jay
    Last edited by JayB0730; Jul 16th, 2019 at 02:54 PM.

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,133
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: PIVOT TABLES: Show correct count value based on different cell values

    hello, Jay

    Seeing the sample source data would help understanding.

    I may be on the 'wrong track' but it sounds to me like excluding data with 'Quantity on Hand' = 0 is what you're after.
    If so, can you put the 'Quantity on Hand' field as a page field and deselect the quantity 0?

    HTH
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


  3. #3
    Board Regular
    Join Date
    Oct 2014
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PIVOT TABLES: Show correct count value based on different cell values

    Hi Fazza,

    Wow, I think I had the biggest "brain fart" known to man. Yes, how I completely missed the fact that I could simply throw the Quantity on Hand field into the reports filter section & 'uncheck' 0, is beyond me. Thank you for giving me the virtual "slap on the back of the head".

    Thank you & take care,
    Jay

  4. #4
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,133
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: PIVOT TABLES: Show correct count value based on different cell values

    Just a minor BF, Jay. Suggest you double-check the solution does exactly what you want. All the best, Fazza
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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