Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Excel 2010 and still no distinct count on pivot tables

anationalacrobat (TechnicalUser) (OP)
3 Feb 10 12:11
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?  
kjv1611 (TechnicalUser)
9 Feb 10 7:24
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

anationalacrobat (TechnicalUser) (OP)
9 Feb 10 10:03
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:


http://www.contextures.com/xlPivot07.html
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:

  =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)

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.  
kjv1611 (TechnicalUser)
9 Feb 10 10:23
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

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