Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table-get unique count | Excel Discussion (Misc queries) | |||
Pivot table: how to count unique? need help! | Excel Discussion (Misc queries) | |||
Count unique values - Pivot Table | Excel Discussion (Misc queries) | |||
Count Unique in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Unique Count | Excel Worksheet Functions |