Count Unique in Pivot Table

[Nelson]

Hi,

I have a large list - and was wondering how to count unique numbers (eg.
customer IDs) in a pivot table.
I have arranged my data so each location is shown in the rows.

Can this be done? So far I can only get the data field to count the total
entries, but not unique entries by location.

If not - is there a way to do this with a formula?

Any help appreciated.

Nelson

 

[Jim Cone]

Nelson,

Add a title directly above the list
Select the title and list
Go to Data | Pivot Table
On the layout view, drag the title to the row field.
AND drag the title to the data field.
"OK" and "Finish" your way out of the pivot Table.

You will have a list of all the numbers with the count for each.
This can be copied and pasted where you want.

Jim Cone
San Francisco, USA


"Nelson" wrote in message

 

[Debra Dalgleish]

A pivot table won't calculate a unique count. You could add a column to
the list, then add that field to the pivot table.

For example, to count unique items in column A, use the following formula:

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

Copy this formula down to all rows in the list, then add the field to
the pivot table.

 

[Nelson]

Thanks Debra, but that formula won't work effectively - I have about 38,000
rows of data to work with - which slows my xls down.

Nelson

 

[Myrna Larson]

The only other option that I can think of is to select the column with the ID
numbers, then use Filter/Advanced Filter, check Copy to Another Location and
Unique Records only. Then you can count the number of items in the new list.

Thanks Debra, but that formula won't work effectively - I have about 38,000
rows of data to work with - which slows my xls down.

Nelson

 

[Nelson]

Ok thanks Myrna - I'll give that a go.

cheers

Nelson

 

[Ken Wright]

Why not just use Debra's suggestion and then copy and paste special the
column as values. That way you'll have the data and see no impact on
performance. Obviously if you change the source data then you need to
repeat the exercise, but any other manual option will likely be more tedious
anyway.