+ Reply to Thread
Results 1 to 9 of 9

Count values of a defined range in a pivot table column

  1. #1
    Registered User
    Join Date
    06-21-2020
    Location
    London
    MS-Off Ver
    macOS Catalina
    Posts
    7

    Count values of a defined range in a pivot table column

    Hi,

    I have a pivot table set up to show data in minutes.

    Screenshot 2020-06-29 at 10.22.15.png

    I already have it set up to show the count of each set of minutes, min, max and average.

    I want to add a column that will count all the values in the first column that lay between 0-30 and then I would like a column next to that that would display the % of that range count from the total.

    For instance, doing a visual count, I know there are 11 values that lay between the 0-30 range, 11 values represents 61.1% of the total (which is 18). I just cant seem to get these values presented the way I need them.

    Many thanks in advance.

  2. #2
    Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    318

    Re: Count values of a defined range in a pivot table column

    Please see the yellow ribbon at the top on how to upload a sample workbook.

    Without seeing your data . . . You could add calculated fields or items to your pivot table.
    Click anywhere in the pivot table, then on top the Pivot Table Tools will show in the ribbon. Select Analyze, Calculations > then drop-down menu 'Fields, Items & Sets'.
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  3. #3
    Registered User
    Join Date
    06-21-2020
    Location
    London
    MS-Off Ver
    macOS Catalina
    Posts
    7

    Re: Count values of a defined range in a pivot table column

    Example workbook
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    318

    Re: Count values of a defined range in a pivot table column

    On Sheet 1 you had a value error in J14 due to the length of cell I14 having 3 digits at the start. Corrected formula.
    I have inserted a count of the records which are between 0-30 minutes (including 0 & 30).
    Percentage does work of the un-filtered total, maybe not quite what you need.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-21-2020
    Location
    London
    MS-Off Ver
    macOS Catalina
    Posts
    7

    Re: Count values of a defined range in a pivot table column

    OK

    I was not expecting you to do the work for me. What I need for an answer is someone to show me HOW to do it.

    If you just do the work for me then I don't learn anything.

    Also the percentages column is wrong. 11/18*100 is 61%, not 31%

    Im less concerned about the percentages column. I get get around that another way. But I need to know how you entered that new 0-30 column and got an accurate count of it.

  6. #6
    Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    318

    Re: Count values of a defined range in a pivot table column

    In Sheet1 I have added a 'helper' column in column N. The formula in N2 is: =IFERROR(IF(K2<=30,1,0),0)
    Lets break it down.
    IF(K2<=30,1,0) says; if the value in K2 is less or equal to 30, then return 1, otherwise return 0.
    I then wrap it in IFERROR which will return 0 of there is a error with the result (as you had the formula error in J). This way we will always have either 1 or 0 as the result.
    We then add this new column to the pivot table range and insert the field to count on how many instances we have 1 (0-30).

    I know the percentage is not what you want. It basically counts the percentage of all records. You have 35, but only show 16 in the pivot table.

    Trust this helps.

  7. #7
    Registered User
    Join Date
    06-21-2020
    Location
    London
    MS-Off Ver
    macOS Catalina
    Posts
    7

    Re: Count values of a defined range in a pivot table column

    Many thanks

    Now if i wanted to add another column showing attendence times between 31-60 minutes. I tried adding the formula =IFERROR(IF(K2>=31<=60,1,0),) but all it returned was 0.

    Am I missing something from the new formula?
    Last edited by Hoareman; 06-30-2020 at 05:58 AM.

  8. #8
    Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    318

    Re: Count values of a defined range in a pivot table column

    Hi again,
    Try: =IFERROR(IF(AND(K2>30,K2<=60),1,0),0)

    You need to use AND for multiple criteria.

  9. #9
    Registered User
    Join Date
    06-21-2020
    Location
    London
    MS-Off Ver
    macOS Catalina
    Posts
    7

    Re: Count values of a defined range in a pivot table column

    Amazing,

    That one works a treat.

    Thanks very much for all your help and for teaching me.

+ 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. Pivot table to count values?
    By martinreed in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-24-2018, 09:32 AM
  2. Replies: 1
    Last Post: 11-19-2015, 10:34 AM
  3. Replies: 6
    Last Post: 08-19-2015, 07:46 PM
  4. How to count distinct values from table in pivot table
    By gopijadhav in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-04-2014, 07:14 AM
  5. Replies: 11
    Last Post: 07-18-2013, 05:14 PM
  6. Replies: 12
    Last Post: 02-06-2012, 11:23 AM
  7. Application defined or object defined error when creating a Pivot Table
    By Kaigi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-02-2009, 05:05 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