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

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

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

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

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