Count Unique in Pivot Table
 

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

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

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




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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


"Debra Dalgleish" wrote in message
...
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 wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.

On Sun, 10 Apr 2005 11:18:53 +0930, "Nelson"
wrote:

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


"Debra Dalgleish" wrote in message
...
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 wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

cheers

Nelson

"Myrna Larson" wrote in message
...
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.

On Sun, 10 Apr 2005 11:18:53 +0930, "Nelson"
wrote:

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


"Debra Dalgleish" wrote in message
...
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 wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Nelson" wrote
in message ...
Ok thanks Myrna - I'll give that a go.

cheers

Nelson

"Myrna Larson" wrote in message
...
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.

On Sun, 10 Apr 2005 11:18:53 +0930, "Nelson"
wrote:

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


"Debra Dalgleish" wrote in message
...
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 wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html