Count Unique in Pivot Table

[Abhay]

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.

 

[Roger Govier]

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

 

[Abhay]

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.