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

[Paul M]

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

 

[chef]

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

 

[Paul M]

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.

 

[Aladin Akyurek]

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.

Something like this...

Book1
	A	B	C	D	E	F	G	H
1	FirmId	State	T2T			SumofT2T
2	1000	NY	1			FirmId	Total
3	1001	NY	0.5			1000	1
4	1001	NJ	0.5			1001	1
5	1002	CA	0.5			1002	1
6	1002	DC	0.5			1003	1
7	1003	NJ	1			1004	1
8	1004	FL	1			1005	1
9	1005	GA	0.5			1006	1
10	1005	NY	0.5			GrandTotal	7
11	1006	FL	1
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&""))

 

[ken2step]

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.

 

[Aladin Akyurek]

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

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

 

[ken2step]

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

 

[Paul M]

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.