# Simple Pivot Table to Count Unique Values

###### Posted By: Anonymous

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I’m grouping on.

For instance, I have this:

```
ABC 123
ABC 123
ABC 123
DEF 456
DEF 567
DEF 456
DEF 456
```

What I want is a pivot table that shows me this:

```
ABC 1
DEF 2
```

The simple pivot table that I create just gives me this (a count of how many rows):

```
ABC 3
DEF 4
```

But I want the number of unique values instead.

What I’m really trying to do is find out which values in the first column don’t have the same value in the second column for all rows. In other words, “ABC” is “good”, “DEF” is “bad”

I’m sure there is an easier way to do it but thought I’d give pivot table a try…

## Solution

Insert a 3rd column and in Cell `C2`

paste this formula

```
=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
```

and copy it down. Now create your pivot based on 1st and 3rd column. See snapshot

###### Answered By: Anonymous

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.