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:
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.