# Pivot Table - How can I get a count of unique data items?

#### Paul M

##### New Member
Hey Guys,

Is it possible to get a count only of unique data items using a pivot table. For example, let's say that a column has 10 firm id numbers but 3 are repetitive, is there a way to get a count of the 7 unique ids.

This list should give you a better idea of what I mean:

Firm id State

1000 NY
1001 NY
1001 NJ
1002 CA
1002 DC
1003 NJ
1004 FL
1005 GA
1005 NY
1006 FL

Thanks

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Re: Pivot Table - How can I get a count of unique data items

you could try filter?

highlight column with ID no......then Data..Filter..Advanced Filter..Click on box for Unique Records only

Regards
Chef

Re: Pivot Table - How can I get a count of unique data items

Thanks chef but that would not solve my problem. I want to do the count within the pivot table, not to extract the data and perform a count based on that extracted data.

Re: Pivot Table - How can I get a count of unique data items

Paul M said:
Thanks chef but that would not solve my problem. I want to do the count within the pivot table, not to extract the data and perform a count based on that extracted data.

Something like this...
Book1
ABCDEFGH
1FirmIdStateT2TSumofT2T
21000NY1FirmIdTotal
31001NY0.510001
41001NJ0.510011
51002CA0.510021
61002DC0.510031
71003NJ110041
81004FL110051
91005GA0.510061
101005NY0.5GrandTotal7
111006FL1
12
13
Sheet1

The formula in C2 is:

=1/COUNTIF(\$A\$2:\$A\$11,A2)

However, why not use a formula outside the pivot table that counts distinct Firm Id's with:

=SUMPRODUCT((A2:A11<>"")/COUNTIF(A2:A11,A2:A11&""))

Re: Pivot Table - How can I get a count of unique data items

Hi Alan.....could you explain the criteria part of the

COUNTIF(A2:A11,A2:A11&"")

I don't understand what the A2:A11&"" is refering to.

Your use of Excel's functions and creativity with them never ceases to amaze me! I hope you don't mind explaining to me.

Re: Pivot Table - How can I get a count of unique data items

ken2step said:
...could you explain the criteria part of the

COUNTIF(A2:A11,A2:A11&"")

I don't understand what the A2:A11&"" is refering to.

... I hope you don't mind explaining to me.

Ken,

I didn't devise this formula myself. Here its history & an explanation how it works:

http://www.mrexcel.com/board2/viewtopic.php?t=73502&highlight=

Re: Pivot Table - How can I get a count of unique data items

Thanks Alan! That one is going to take some time to soak up for sure!

Ken

Re: Pivot Table - How can I get a count of unique data items

Thanks guys, I apologize for not framing the question correctly. I will repost.

Replies
1
Views
166
Replies
3
Views
242
Replies
3
Views
172
Replies
1
Views
202
Replies
7
Views
232

1,206,755
Messages
6,074,757
Members
446,084
Latest member
WalmitAal

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back