Count Only Unique numbers in a pivot table

Within the data field of a pivot table, I know we can sum, count, average, etc,...but is there a way to get a count of unique entries/numbers there?

Any help is much appreciated.

Thanks!

No you can't.

You will need to create an extra column in the source table to display uniques, and extend the data source table to include the new column.
In the new column use a formula like
=IF(COUNTIF(A:A,A1)=1,1,0)

That will count only where unique and totally ignore all duplicated cells - most people want a distinct count so that duplicates are counted once.

so if you have 1000 rows use ...    =IF(COUNTIF(A1:A$1000,A1)=1,1,0)   

I am really not understanding how this solution and others that are keep finding by googling are supposed to work.  All this formula does is (very slowly) indicate the first occurrence of a repeating item.  But in a pivot table, the intersection of pivots could result in a set of rows NONE of which include the first occurrence. Summing up the "first_unique" column in that case will simply result in a zero.  The issue here is that "pivot after unique"   -- which is what you are suggesting -- is not the same as "pivot after unique".  Am I missing something here?

the last statement should read "pivot after unique" is not the same as "unique after pivot"

Hi

There was a mistake in my original formula as posted.

=IF(COUNTIF(A:A,A1)=1,1,0)

should have been

=IF(COUNTIF($A$1:A1,A1)=1,1,0)

If you are still having difficulties, mail me a copy of uor workbook.

roger at technology4u dot co dot uk

Change the at and dots to make valid email address.

I signed up for this forum just to say this: why in the world is this simple feature still not built in to Excel? How many versions since Excel's existence, and we still can't count unique items in a pivot table?

Hello,

Waiting for an excel version implementing count, I wrote a code improving your pivot so it does provide unique count on the field you choose.

It's just code to paste one time.

let me know if it works for you.

http://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html

Hi

It would be nice if you could attach your file here (or on your blog) for downloading, as it would then be easier to see what unique items you were counting

Hello Roger,

I will try to upload a file.

I was always wondering where I could find dummy datas to create example, if you know.

The code it self let you choose what field you want to count unioque items.

Regards

update: http://share1t.com/3zu6fl

unique number of songs by artist, year, month.

in fact a bad exemple as song only appears once in the source data.

I will try to find something better

hi,

I tried your version and it didn't work. I attached a file for your record.

I want to show a count of the company names that Dan Brown handle (10 companies). But since there's duplicates in other months, it sums the name up. I want to remove the duplicate and count it only once. Is there a way to do that in the pivot table?

Hi

You need to change the attribute of Homogenized C. Conut to SUM not COUNT.

Then you will see
Brown  10
Krista   22
Smith  38

Alternatively, if you want a coded solution, then look at the posting by Lazyvba where he has posted some excellent code top do this