Counting Unique Entries

[Tendresse]

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,"=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

 

[Max]

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

 

[Tendresse]

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

 

[Max]

Glad to hear that. You're welcome.