Count Unique Values in a Pivot Table

[audiate]

Hello there

I have a spreadsheet with several records for each person's name.
I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name.

When I set up a basic Pivot, it counts each occurence of the person's name.

Is there some way to have it count unique values only?

Thanks

[AlohaJulio]

If you put the field with the names into the data section, you should be able to get the number of names as a count.

[LTunnicliffe]

Try adding the persons name twice once as a row heading and once as a data field (count). Here's my example but my pivot table didn't come out formatted by I'm using HTLMMakerLite.

******** ******************** ************************************************************************>

Microsoft Excel - Book2 ___Running: xl97 : OS = Windows NT 4
File Edit View Insert Options Tools Data Window Help About
A1 =
	A	B	C	D	E	F	G
1	SalesPerson	Rev	*	SalesPerson	Data	Total	*
2	sam	100	*	bob	Count*of*SalesPerson	3	*
3	sam	101	*	*	Sum*of*Rev	320	*
4	pam	102	*	pam	Count*of*SalesPerson	5	*
5	pam	103	*	*	Sum*of*Rev	526	*
6	pam	104	*	sam	Count*of*SalesPerson	6	*
7	bob	105	*	*	Sum*of*Rev	645	*
8	bob	106	*	Total*Count*of*SalesPerson	*	14	*
9	pam	107	*	Total*Sum*of*Rev	*	1491	*
10	sam	108	*	*	*	*	*
11	bob	109	*	*	*	*	*
12	pam	110	*	*	*	*	*
13	sam	111	*	*	*	*	*
14	sam	112	*	*	*	*	*
15	sam	113	*	*	*	*	*
16	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*
cashflow *

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hope this helps!

[audiate]

Thanks for your reply, but I think you didn't quite understand the question.

I want each PERSON only counted ONCE. I dont' want 2 of Bob and 5 of Pam;
basically I need to do pivots by different categories, but just show how many people (1 count for 1 person) fall under each category.

[AlohaJulio]

When you put the name field in the data section, double click on it, choose Count, then click options and choose index. That should give you the count of the unique names, not a count of names

[audiate]

Thank you thank you thank you!!

(now why can't they have that in the help menu??)

[audiate]

I just found out something...

I tried out the Index function and at first thought it had solved my problems.

However, all the subtotals now show as a "1" as well.

******** ******************** ************************************************************************>

Microsoft Excel - Page.htm ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
H15 =
	A	B	C	D
1	DATA	*	*	*
2	Name	Category	Date	Award*Amount
3	Ben*Smith	A	12-Jan-03	*************15,000*
4	Jan*Fehr	A	02-May-02	*************50,000*
5	Ben*Smith	A	03-Sep-01	*************90,000*
6	Armand*Moore	C	13-Oct-02	*************23,000*
7	*	*	*	*
8	PIVOT	*	*	*
9	*	*	Data	*
10	Category	Name	Count*of*Name*-*Index	Count*of*Name*-*Normal
11	A	Ben*Smith	1	2
12	*	Jan*Fehr	1	1
13	A*Total	*	1	3
14	C	Armand*Moore	1	1
15	C*Total	*	1	1
16	Grand*Total	*	1	4
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

In the example, under "Count of Name - Index" each name is counted once but the subtotal for category A shows as 1 instead of 2.
If I change it back to the normal "count" (next column) my subtotals are still wrong because it is now counting every instance of the name.

Solutions???

[judas]

I am having the same problem you are...did you find a work around for this? Now I have to do two pivot tables in order to count the unique person (im trying to find out number of patients per day from a DB...each row of the DB corresponds to a study, and a patient can have several studies / day).

Edit: For the record: I did the trick as described here:

http://www.contextures.com/xlPivot07.html

Count Unique Items

In a pivot table, you may want to know how many unique customers placed an order for an item, instead of how many orders were placed. A pivot table won't calculate a unique count. However, you could add a column to the database, then add that field to the pivottable.

For example, to count the unique occurences of a Customer/Item order, add a column to your database, with the heading 'CustItem'

In the first data row, enter a formula that refers to the customer and item columns. For example:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)

Copy the formula down to all rows in the database.

Then, add the field to the data area of the pivot table.

[Aladin Akyurek]

Hello there

I have a spreadsheet with several records for each person's name.
I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name.

When I set up a basic Pivot, it counts each occurence of the person's name.

Is there some way to have it count unique values only?

Thanks

Something like...

Item	Value	D-Score			Item	Data	Total
a	3	0.25			a	Sum of Value	17
a	4	0.25				Sum of D-Score	1
b	2	1			b	Sum of Value	2
c	6	0.5				Sum of D-Score	1
d	7	1			c	Sum of Value	10
a	8	0.25				Sum of D-Score	1
c	4	0.5			d	Sum of Value	7
a	2	0.25				Sum of D-Score	1
					Total Sum of Value		36
					Total Sum of D-Score		4

A1:B9 houses the source data.

C1: D-Score

C2, just enter and copy down:

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

Then build a pivot table, where Total Sum of D-Score would constitute the unique count.

Also:

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(A2:A9<>"",MATCH("~"&A2:A9,A2:A9&"",0)),ROW(A2:A9)-ROW(A2)+1),1))

would yield that count.

[WaxonWaxov]

When you put the name field in the data section, double click on it, choose Count, then click options and choose index. That should give you the count of the unique names, not a count of names

I don't see the options thing you reference. I am using Excel 2007. Is this something else the folks at MSFT took away from us.

I desperately need the answer to this question.