Count Unique in Pivot Table
 

I have a Excel sheet with data like
OBS name hours
obs1 abb 10
obs1 abb 12
obs1 abc 15

End result I would like to see is
obs 1 , count of unique name, sum of hours
obs1, 2, 37

In existing Pivot table if I use Count on name field it returns 3 as there
are three rows. I wish to have count on unique Name per obs. Thanks for help.

Count Unique in Pivot Table
 

Hi

You could add a 4th column to your data, titled Unique
In cell D2 enter
=IF(COUNTIF($B$2:$B2,B2)1,"",COUNTIF($B$2:$B2,B2) )
and copy down for the extent of your data.

In the PT, drag Unique to the Data area as SUM of Unique and you will get a
result of 2
If you use Count, then it will still return a result of 3

--
Regards
Roger Govier



"Abhay" wrote in message
...
I have a Excel sheet with data like
OBS name hours
obs1 abb 10
obs1 abb 12
obs1 abc 15

End result I would like to see is
obs 1 , count of unique name, sum of hours
obs1, 2, 37

In existing Pivot table if I use Count on name field it returns 3 as there
are three rows. I wish to have count on unique Name per obs. Thanks for
help.

Count Unique in Pivot Table
 

Thanks Roger. This helps for sure, I was wondering if there is smarter way in
PT.
I could not get why second COUNTIF is used though.


"Roger Govier" wrote:

Hi

You could add a 4th column to your data, titled Unique
In cell D2 enter
=IF(COUNTIF($B$2:$B2,B2)1,"",COUNTIF($B$2:$B2,B2) )
and copy down for the extent of your data.

In the PT, drag Unique to the Data area as SUM of Unique and you will get a
result of 2
If you use Count, then it will still return a result of 3

--
Regards
Roger Govier



"Abhay" wrote in message
...
I have a Excel sheet with data like
OBS name hours
obs1 abb 10
obs1 abb 12
obs1 abc 15

End result I would like to see is
obs 1 , count of unique name, sum of hours
obs1, 2, 37

In existing Pivot table if I use Count on name field it returns 3 as there
are three rows. I wish to have count on unique Name per obs. Thanks for
help.