Count number of time names appears

D

da

I have columns a thru I and following names appear in each column. How can I
count how many times each name appears in the range?
Thanks

Al-Amin Al-Amin Al-Amin Al-Amin Al-Amin Al-Amin Al-Amin Al-Amin Al-Amin
Andrews, D. Andrews, D. Andrews Andrews Andrews Andrews Andrade, E Andrade,
E Andrade, E
Aparicio Aparicio Aparicio Aparicio Aparicio Aparicio Aparicio Andrade,
L Aparicio
Armenta Armenta Armenta Armenta Armenta Armenta Armenta Aparicio Armenta
Autry Autry Autry (1) Autry (1) Autry (1) Autry (1) Autry Armenta Autry
Barot Bean Avalos, R Blue Ayala/2009 Avalos, R/12-11-08 Avalos,
R Autry Avalos, R
Blue Carbajo Carbajo (2) Carbajo (2) Bean-11/29 Blue Blue Avalos, R Blue
Carbajo Carlos, A. Carlos, A. Carlos, A. Blue Carlos, A. Carlos,
A. Ayala/2/23/09 Carlos, A.
Carlos, A. Carr Carr Carr Carlos, A. Carr Carr Blue Carr
Carr Castro Castro Castro Carr Castro Castro Carlos, A. Castro
Castro Chen Chen Chen Castro Chen Chen Carr Chen
Chen Clark, D Denniston Duenez Chan, A-12/11 Clark, D Co Castro Co
Denniston Elizarraraz Elizarraz Farias Chen Co Dickson Chen Dear
Dickson Farias Farias Garcia, E Clark, D-10/30 Colon,
C/4-1-09 Duenez Co Dickson
Elizarraraz Garcia Garcia, E Gonzalez, C Co Cortinas/2-6-09 Ector,
L/PMDL Dickson Duenez
Farias Gonzalez, C Gonzalez, C Gonzalez,
J. Duenez Delgado/3-17-09 Farias Duenez Farias
Garcia Gonzalez, J. Gonzalez, J. Haro Farias Duenez Garcia, E
Farias Garcia, E
Gonzalez, C Harris, E Harris Harris Garcia, E Farias Gonzalez,
C Garcia, E Gonzalez, C
Gonzalez, J. Hawkins Hawkins Hawkins Gonzalez, C Garcia, E Gonzalez, J.
Gonzalez, C Gonzalez, J.
Hawkins Joseph, B Joseph, B Joseph, B Gonzalez, J. Gonzalez,
C Haro Gonzalez, J. Haro
Jones, D. Krockle Matsumoto Lopez, R Haro Gonzalez, G/2-23-09 Hawkins
Haro Hawkins
Joseph, B Matsumoto McKinley Low (4) Harris Gonzalez, J. (4) Jones,
D Hawkins Jones, D
Matsumoto Perez, Joe Mosquera Mendez Hawkins Haro Joseph, B Jones,
D Joseph, B
Moore Ramsey Ramsey Mojica-Ocana Joseph, B Harris Kilayko Joseph,
B Kilayko
Ramsey Rouzan, R Rouzan, Ray Perez, Joe Krockel-11/14 Hawkins Low Kilayko
Low
Rouzan, R Salamanca Salamanca Rabon Low (4) Jones,
M/4-29-09 Lozano Low Lozano
Salamanca Sanchez, S. Sanchez, S Ramsey Mendez Joseph,
B Mendez Lozano Mendez
Sanchez, S. Scarborough Scarbourough Rouzan,
Ray Peaks/2009 Krockel Mojica-Ocana Mendez Mojica-Ocana
Scarborough Selby Selby Salamanca Perez, Joe Low (3) Moore,
T Mojica-Ocana Moore, T
Selby Shamblin Shamblin Saxon-10/30 Rabon Mendez Morales, R Moore,
T Morales, R/PMDL
Shamblin Smith, C Smith,
C Scarbourough Ramsey Mosquera/12-10-08 Mosquera Morales, R/PMDL Mosquera
Smith, C So So Selby Renard Perez, Joe Perez, Joe Mosquera Perez, Joe
So Soto Soto (3) Shamblin Rouzan, Ray Rabon Rabon Peaks/2/5/09 Rabon
Soto Ta Ta Smith, C Salamanca Ramsey Ramsey Perez, Joe Ramsey
Ta Tung Tung So Scarbourough Renard Renard Rabon Renard
Tung Wu Unruh Soto (3) Selby Rouzan, Ray Rouzan, Ray Ramsey Rouzan, Ray
Wu Vasquez, D Valdenor Shamblin Salamanca Salamanca Renard Salamanca
Wu Wilkerson Smith, C Scarbourough Santis Rouzan, Ray Santis
Williams, A So Selby Selby Salamanca Selby
Womble-11/14 Soto (3) Shamblin Smith, C Santis Smith, C
Wu Torres, G Smith, C Torres, G Selby Smith, J/VNYS
Valdenor Smith, F/3-4-09 Torres, R. Smith, C So
Williams, A So Valdenor Smith, J/VNYS Torres, G
Wu Soto (2) Williams, A So Torres, R.
Torres, G Wu Torres, G Valdenor
Valdenor Zednegle Torres, R. Williams, A
Williams, A Valdenor Wu
Wu Wilkerson
Williams, A
Wu
 
D

da

Thanks
But do I have to type each name in the formula? There are about 80 names in
the range.
 
J

Jacob Skaria

1. Copy the data to column A then select the range in Col A. You need to have
header in col A
2. From menu Data>Filter>Advanced Filter>Copy to another location
3. In copy to I have selected B1 and check 'Unique records only'
4. Click OK will give you the unique list of names
5. In C2 apply the below formula


If this post helps click Yes
 
D

da

Thank you
However, I am still not getting the result I want.
I want to count how many times a name appears in a range A2:A555. How can I
get answer I want?
thanks
 
D

da

Thank you
However, I am still not getting the result I want. I want to count the
number of times a name appears in a range A2:A555.
The result should be something like:
Smith 5
John 7
Adamn 3
 
B

Bernd P

Hello,

There is no need to list (and later to keep track of) all your unique
entries/names.

I suggest to select a sufficiently long area of rows and two columns
and to array-enter:
=Pfreq(TRANSPOSE(returnnonempty(A1:I999)))

Pfreq is a UDF which you can find here:
http://sulprobil.com/html/pfreq.html

ReturnNonEmpty you will find here:
http://sulprobil.com/html/concatenate.html

A COUNTIF approach is suboptimal here and should only be used if you
cannot use VBA. Please see http://sulprobil.com/html/countif.html

Regards,
Bernd
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count unique names 1

Top