Unique Records in Pivot Tables

Deb T · Apr 2nd, 2002, 02:35 PM

I need to do a count of unique records in a pivot table. Every time I use the COUNT function it counts all the data? Is there an easy way to get the count of unique records? HELP!!

p.s. the records are numeric

Mark W. · Apr 2nd, 2002, 02:55 PM

Suppose cells A1:A11 contain...

{"Field1"
;10
;10
;30
;10
;25
;10
;25
;30
;30
;15}

For Field2 (B2:B11) enter the formula, =1/COUNTIF($A$2:$A$11,A2), into cell B2 and copy down.

Create a PivotTable with Sum of Field2 in the DATA area. As you can see there are 4 unique Field1 values.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 02:35 PM	#1
Deb T New Member Join Date: Apr 2002 Posts: 2	I need to do a count of unique records in a pivot table. Every time I use the COUNT function it counts all the data? Is there an easy way to get the count of unique records? HELP!! p.s. the records are numeric

Apr 2nd, 2002, 02:55 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Suppose cells A1:A11 contain... {"Field1" ;10 ;10 ;30 ;10 ;25 ;10 ;25 ;30 ;30 ;15} For Field2 (B2:B11) enter the formula, =1/COUNTIF($A$2:$A$11,A2), into cell B2 and copy down. Create a PivotTable with Sum of Field2 in the DATA area. As you can see there are 4 unique Field1 values.