# Count values of a defined range in a pivot table column

1. ## 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.  Register To Reply

2. ## 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'.  Register To Reply

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

Example workbook  Register To Reply

4. ## 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.  Register To Reply

5. ## 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.  Register To Reply

6. ## 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.  Register To Reply

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?  Register To Reply

8. ## 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.  Register To Reply

9. ## 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.  Register To Reply