Menu
  1. Accueil
  2. How To
  3. Software
  4. Office Software
  5. Excel
Thanks
9
Thanks

A few words of thanks would be greatly appreciated.

Excel - A Formula to Count a List of Names

Latest update on by David Webb .

For users who are struggling with handling Microsoft Excel when trying to copy the same name multiple times without making it confusing, a simple procedure needs to be followed in order to count a list of names. A list of names in a report may be generated, where the same name appears multiple times. With column 'A' having a list of names, where the same name is repeated, and the user desires to display all the names in column 'B', but only once.


With this issue, it is required to use the Filter and Advanced Filter options from the Data menu dropdown list. Using the radio button and the 'copy-to' options, it is required to choose the 'unique records only' option to solve this issue.

Excel Formula to Count List of Names - Example

You have a list of names that will change as reports are generated. The report will include the same name multiple times, so the name joebloggs could appear 10 times. You need a formula to scan COL:A that has the list of names. In COL:B you would like Excel to display each of the different names that are in COL:A but only once. So, for example, JOEBLOGGS would appear in B2 only once. Then in COL:C you would like the total amount of times that name appeared in that list. So for example: 

A B C       
1 joebloggs JOEBLOGGS 5       
2 joebloggs       
3 joebloggs       
4 joebloggs       
5 joebloggs


These names will vary so you cannot specify the names using countif function. You need Excel to populate the names in B automatically.

Solution

Suppose your data is like this from A1 to A9 (note column heading - this is necessary) 

Names
a       
a       
a       
a       
s       
s       
d       
d
  • Click on Data(menu)-Filter-AdvancedFilter
  • Choose radio button at the top "copy to another location"
  • Next to the list range click on the icon at the right end of the small window and highlight A1 to A9
  • Leave blank criteria range
  • Next to "copy to"-click on the icon at the right end of the window and select some empty cell e.g. D1
  • Choose "unique records only" at the bottom left
  • Click on OK
  • you will get D1 to D4 names 

Names
a       
s       
d

in E2 copy paste this formla (repeat E2) 
=COUNTIF(A$2:A$9,D2)

copy E2 down.

You will get names frequency 
Names
a 4       
s 2       
d 2

Image: © Dzmitry Kliapitski - Shutterstock.com
Thanks
9
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
Related
This document, titled « Excel - A Formula to Count a List of Names », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).
2 vote - 5.0/5 2 Comments
Report

Thank you so much - I needed this to count the number of customers who had ordered certain comics and I couldn't find anywhere else that explained the process of counting unique names. Thank you!
Report

We are planning for Elders and Deacons at church for the year. I wanted to have easy way to count how many times each person volunteered. Thank you for this example!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!