Counting strings in excel with Pivot

[navin]

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

 

[Guest]

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

 

[bplumhoff]

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

 

[Debra Dalgleish]

You can add a field to the source data, as described here:

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.