• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Counting Unique values in pivot table

jdhillon

New Member
i have got thousands of time entries against couple of hundered people on different dates, trying to calculate how many people were present on a particular day. when i make pivot and ..it counts number of entries not names..e.g. if somebody worked 8hrs straight time and 2 hrs overtime..it is counting both separtely...hence counting people twice..any help would be highly appreciated and you'll make my life better..

regards,
 
JDHillon


This might be best done outside the pivot table, as the Pivot Tables whole role is to accumulate values where items are the same.


Use an Advanced Filter and Copy Unique Records

Then use a Countif() formula to count the original list against each entry in the Unique list
 
Thanks..but it's very difficult to filter date by date i have data for almost 6months and still accumulating..
 
JDHillon


What part of Hui's suggestion makes using a filter difficult? You should be able to setup an Advanced Filter using one criteria (the date interested in), and the Unique list option is a check in the box (literally). For help on filter, I'd recommend Debra's site:

http://www.contextures.com/xladvfilter01.html


If you wanted to get somewhat fancy, you could record a short macro of you applying the filter. Then, as you add data in the future, rather than having to update your PivotTable, you can just run the macro (should take 1 to 2 seconds) to get your list of people present on a particular day.


PS. Note that Hui's original suggestion was to filter for all the data and then get a formula to find a specific day, whereas mine is to filter to a specific day. Both have their merits, it's your preference.
 
Back
Top