Hi all,

Column D contains staff members names. I would like to be able to count the
number of times each member was listed. For example, if column D is as
follows:

D
Smith
David
Smith
Roberts
Baker
Smith

I would like to get the following result:
Employee 'Smith' listed '3' times
Employee 'David' listed '1' time, etc.

Because i don't have a pre-identified list of employees, i can't use the
formula:
=COUNTIF(D:D,"=Smith")

Is there a formula that can:
- Display the Unique Values and
- the number of times each one of them appeared

I use Excel 2003

Many thanks in advance
Tendresse

One quick way ... try a pivot table (PT). Assume the col of names is running
in A2 down, with A1 containing a col label, say: "Name". Select col A, click
Data Pivot Table ... Click Next Next. In step 3, click Layout, then drag
n drop "Name" into ROW, then again into DATA (it'll appear as Count of
Name),
click OK Finish. That's it. Hop over to the created PT sheet (it'll be
placed just to the left of the source sheet) for the results, viz.:

Count of Name
Name Total
Baker 1
David 1
Roberts 1
Smith 3
(blank)
Grand Total 6

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tendresse" wrote:
Hi all,

Column D contains staff members names. I would like to be able to count the
number of times each member was listed. For example, if column D is as
follows:

D
Smith
David
Smith
Roberts
Baker
Smith

I would like to get the following result:
Employee 'Smith' listed '3' times
Employee 'David' listed '1' time, etc.

Because i don't have a pre-identified list of employees, i can't use the
formula:
=COUNTIF(D:D,"=Smith")

Is there a formula that can:
- Display the Unique Values and
- the number of times each one of them appeared

I use Excel 2003

Many thanks in advance
Tendresse

Thanks, Max. That worked very well. Much appreciated.

"Max" wrote:

One quick way ... try a pivot table (PT). Assume the col of names is running
in A2 down, with A1 containing a col label, say: "Name". Select col A, click
Data Pivot Table ... Click Next Next. In step 3, click Layout, then drag
n drop "Name" into ROW, then again into DATA (it'll appear as Count of
Name),
click OK Finish. That's it. Hop over to the created PT sheet (it'll be
placed just to the left of the source sheet) for the results, viz.:

Count of Name
Name Total
Baker 1
David 1
Roberts 1
Smith 3
(blank)
Grand Total 6

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tendresse" wrote:
Hi all,

Column D contains staff members names. I would like to be able to count the
number of times each member was listed. For example, if column D is as
follows:

D
Smith
David
Smith
Roberts
Baker
Smith

I would like to get the following result:
Employee 'Smith' listed '3' times
Employee 'David' listed '1' time, etc.

Because i don't have a pre-identified list of employees, i can't use the
formula:
=COUNTIF(D:D,"=Smith")

Is there a formula that can:
- Display the Unique Values and
- the number of times each one of them appeared

I use Excel 2003

Many thanks in advance
Tendresse

Glad to hear that. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Tendresse" wrote in message
...
Thanks, Max. That worked very well. Much appreciated.