+ Reply to Thread
Results 1 to 11 of 11

Pivot Table not counting properly from table

  1. #1
    Registered User
    Join Date
    06-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    75

    Pivot Table not counting properly from table

    Hello there!

    In the attached file there is a simple table and pivot. In the pivot table I am attempting to show the 'count' of times there is an instance of a team being late. The highlighted (orange) cells indicate when they are late, and an empty cell indicates they were not late. However, the way it is calculating the total is incorrect as it is including empty cells in the total count. Is there a way to tell why this might be happening? It is not normal behavior and I assume it is something simple that I am missing.

    Thanks!
    Attached Files Attached Files
    Last edited by cph020283; 10-27-2020 at 01:20 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Pivot Table not counting properly from table

    A Power Query Solution:

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    A
    B
    1
    team July
    2
    CSC - Email
    2
    3
    CSC - Phone
    9
    4
    Cards
    4
    Sheet: Sheet2

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    06-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    75

    Re: Pivot Table not counting properly from table

    Hi Alan, I apologize but can you provide more background on using PQ to solve for this? I would like to understand why the pivot table is not correctly counting the instances of late.

    Thanks!

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Pivot Table not counting properly from table

    In the Mcode, this line of action

    Please Login or Register  to view this content.
    Filtered out any row in the Late Column in which there was no value.

  5. #5
    Registered User
    Join Date
    06-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    75

    Re: Pivot Table not counting properly from table

    Thank you Alan! I am a bit unfamiliar with Mcode - how can I edit/remove this so that the pivot works properly?

    Thanks!

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Pivot Table not counting properly from table

    Count counts anything that isn't blank - a formula that returns "" is not a blank cell. All you really need to do is change the aggregating function to Count numbers, rather than just Count.
    Rory

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Pivot Table not counting properly from table

    Did you look at the links provided in my original response on using Power Query.

  8. #8
    Registered User
    Join Date
    06-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    75

    Re: Pivot Table not counting properly from table

    Quote Originally Posted by rorya View Post
    Count counts anything that isn't blank - a formula that returns "" is not a blank cell. All you really need to do is change the aggregating function to Count numbers, rather than just Count.
    That did the trick! Thank you!

    Also, just out of curiosity how can a formula return a blank without using "" as a return value?

    Thanks!

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Pivot Table not counting properly from table

    It can't. Only an empty cell is considered blank.

  10. #10
    Registered User
    Join Date
    06-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    75

    Re: Pivot Table not counting properly from table

    Quote Originally Posted by alansidman View Post
    Did you look at the links provided in my original response on using Power Query.
    I am familiar with how to use PQ (and use it in my main report where my question is derived), however I was not looking for a PQ solution to my challenge. I was hoping to understand why it wasn't counting the number of instances properly. Apologies if there is some confusion here.

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Pivot Table not counting properly from table

    No apologies necessary. Glad you have it figured out.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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 not refreshing properly
    By j_Southern in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-23-2019, 09:16 AM
  2. Pivot table not sorting properly
    By Dast in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 06-10-2016, 06:43 PM
  3. Pivot Table not Totaling Properly
    By buckle2600 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-08-2012, 11:34 AM
  4. Pivot Table Won't Count Properly
    By bystarlight in forum Excel General
    Replies: 4
    Last Post: 07-30-2010, 07:12 AM
  5. Can't Use Pivot Table Properly?
    By jdomante in forum Excel General
    Replies: 3
    Last Post: 11-06-2009, 12:36 PM
  6. pivot table counting blank cells in data table
    By nicko54 in forum Excel General
    Replies: 4
    Last Post: 09-23-2008, 11:26 AM
  7. Replies: 2
    Last Post: 09-23-2008, 11:01 AM

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