Count number of time names appears

[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

 

[Jacob Skaria]

=COUNTIF(A:I,"Al-Amin")

If this post helps click Yes

 

[da]

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

 

[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

 

[Bob Phillips]

Put them in a list, say L1:L80 and use

=COUNTIF(A:I,L1)

and copy down.

 

[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

 

[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

 

[Bob Phillips]

Then

=COUNTIF($A$2:$A$555,L1)

 

[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