Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting strings in excel with Pivot
Hi,
i have this sheet which contains user information. User name in the sheets are repeated. I have used pivot tables to display this data. However, I also want a column which shows the total number of users. Currently, when I select "Count of user name" in the pivot, it counts all the existing user names but i want it to display only distinct user name (i.e), it should not count the repeated the username. For Example: Row: A Mike Mike Mike Mike Mike Mike navin navin navin navin navin For the above row i want Pivot to calculate and show total# of mike and total# of navin Please help. Regards, Navin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting strings in excel with Pivot
Hi,
In the other sheet right the name and put coutif beside of them like below: A B 1 Mike =countif(sheet1!$A$1:$A$100,A1) 2 Navin 3 assume your data is in sheet1 in the range A1 to A100 and then you can copy paste the formula to other names Thanks, Farhad -- Farhad Hodjat "navin" wrote: Hi, i have this sheet which contains user information. User name in the sheets are repeated. I have used pivot tables to display this data. However, I also want a column which shows the total number of users. Currently, when I select "Count of user name" in the pivot, it counts all the existing user names but i want it to display only distinct user name (i.e), it should not count the repeated the username. For Example: Row: A Mike Mike Mike Mike Mike Mike navin navin navin navin navin For the above row i want Pivot to calculate and show total# of mike and total# of navin Please help. Regards, Navin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting strings in excel with Pivot
Hello Navin,
Does it need to be a Pivot table? If a UDF is acceptable, too: http://www.sulprobil.com/html/listfreq.html Regards, Bernd |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting strings in excel with Pivot
Pivot should get you your desired result. Put Users in the "row" area and
Count of Users in "data" area. For example: Users Trevor Dave Tony Dave James James Tony Dave George James Tony Nigel Trevor Tony Nigel Nigel Trevor Trevor James James Nigel Nigel George Trevor Dave James Dave Trevor Dave Trevor will produce the following: Count of Users Users Total Dave 6 George 2 James 6 Nigel 5 Tony 4 Trevor 7 Grand Total 30 -- Gary''s Student "navin" wrote: Hi, i have this sheet which contains user information. User name in the sheets are repeated. I have used pivot tables to display this data. However, I also want a column which shows the total number of users. Currently, when I select "Count of user name" in the pivot, it counts all the existing user names but i want it to display only distinct user name (i.e), it should not count the repeated the username. For Example: Row: A Mike Mike Mike Mike Mike Mike navin navin navin navin navin For the above row i want Pivot to calculate and show total# of mike and total# of navin Please help. Regards, Navin |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting strings in excel with Pivot
You can add a field to the source data, as described he
http://www.contextures.com/xlPivot07.html#Unique and use that field to count the user names. For your example, you could change the formula to COUNTIF: =IF(COUNTIF(B$1:B2,B2)1,0,1) where B is the column with the names, with the formula entered in row 2. navin wrote: Hi, i have this sheet which contains user information. User name in the sheets are repeated. I have used pivot tables to display this data. However, I also want a column which shows the total number of users. Currently, when I select "Count of user name" in the pivot, it counts all the existing user names but i want it to display only distinct user name (i.e), it should not count the repeated the username. For Example: Row: A Mike Mike Mike Mike Mike Mike navin navin navin navin navin For the above row i want Pivot to calculate and show total# of mike and total# of navin Please help. Regards, Navin -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display the source for a pivot table page field | Excel Worksheet Functions | |||
Excel 97 Question (Pivot tables) | Excel Discussion (Misc queries) | |||
Running Excel 2000 VBA Application on Excel 2003 | Excel Worksheet Functions | |||
How to refresh a Excel workbook with pivot & ADO query report in batch mode | Excel Discussion (Misc queries) | |||
convert excel list to pivot table | Excel Discussion (Misc queries) |