Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Excel 2010 and still no distinct count on pivot tables

Excel 2010 and still no distinct count on pivot tables

Excel 2010 and still no distinct count on pivot tables

Yes, there are kludge workarounds but nothing that would work so well as proper support. UGH! What's it going to take to get this feature?  

RE: Excel 2010 and still no distinct count on pivot tables

Are you using the MS customer experience/feedback system they have in the Beta?  If so, mention it to them via that tool.  it can't hurt, whether it happens or not. wink

Forgive me for being dense, but could you further explain what you mean in your comment/rant?  Are you SURE it's something that can't be done.... or are you SURE it's something that SHOULD be done?

I suppose you mean so that you can eliminate duplicates based on one column of your data.  I'd guess that could be accomplished via grouping, so long as the records are true duplicates.


"If to err is human, then I must be some kind of human!" -Me

RE: Excel 2010 and still no distinct count on pivot tables

the Excel developers said it's their most requested new feature and no, they won't be adding it. But ooh, look! Tighter Sharepoint integration. Shiny.

Here's what I'm talking about:

Count Unique Items

In an Excel pivot table, you may want to know how many unique customers placed an order for an item, instead of how many orders were placed. A pivot table won't calculate a unique count. However, you could add a column to the database, then add that field to the pivottable.

For example, to count the unique occurences of a Customer/Item order, add a column to your database, with the heading 'CustItem'

In the first data row, enter a formula that refers to the customer and item columns. For example:


Copy the formula down to all rows in the database.

Then, add the field to the data area of the Excel pivot table.

In this example, you can see that nine unique customers placed an order for binders, and there were 14 orders for binders.

The problem is that these calculations can really slow down a sheet. And you have to create a custom formula for creating distinct counts in different timespans. The example I'm working on is an attendance list for events. Sheet 1 are the names of people, sheet 2 links the names from sheet 1 with activities and a date. I can then run a pivot table to summarize those attendances. The problem comes in when trying to do my counts.

1. How many times has someone attended an activity in a year?
2. How many unique participants do we have in a month?
3. How many unique participants in each activitity do we have?
4. How many unique participants in the year?

I have to have a separate summary column for each count and it becomes a nightmare. The people working with the sheet love the ease of data entry but this whole thing should have probably been done in Access. Those extra counts weren't in the original request but got added in halfway trhough the project.  

RE: Excel 2010 and still no distinct count on pivot tables

Well, if you're stucking handling it in Excel, is it possible to use MS Query to get what you need instead of formulas/pivot tables?  I've not used it very often, but I know that when it can be used, it does seem to offer some performance benefits.


"If to err is human, then I must be some kind of human!" -Me

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close