1. ## Pivot table (2000)

I am trying to get a count of client numbers in a pivot table. The client number can be repeated more than once. Is there a way to count unique client numbers in a pivot table?

2. ## Re: Pivot table (2000)

In general, you can find the number of unique items in a range using the formula

=SUM(1/COUNTIF(range,range))

entered as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of Enter. Can you use that?

3. ## Re: Pivot table (2000)

You can add a formula after the fact to get the number of items in that column...

Steve

4. ## Re: Pivot table (2000)

I am trying to use this formula but I don't think I am entering it right. =sum(1/countif(range,range)) and then ctrl shift enter. How is the range, range part supposed to be entered. My range is A2:A11882.

5. ## Re: Pivot table (2000)

=SUM(1/COUNTIF(A2:A11882,A2:A11882))

But be aware, this 1 formula has many many calculations inherent in it and could make your sheet very "sluggish" (maybe even downright slow)

Steve

6. ## Re: Pivot table (2000)

It would be
<code>
=SUM(1/COUNTIF(A2:A11882,A2:A11882))
</code>
It won't work if you have empty cells and/or zero values in the range A2:A11882. If so, change the formula to
<code>
=SUM(IF(LEN(A2:A11882),1/COUNTIF(A2:A11882,A2:A11882)))
</code>
again entered as an array formula. (Thanks to <!profile=Aladin Akyurek>Aladin Akyurek<!/profile> for this variation.)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•