1

I am trying to count how many times the names of all employees shows up on this worksheet. However, I do not want to have to do a COUNTIF for every single employee as this would take far too long.

What I would like is two columns, the first with the name of each employee listed and the 2nd with the number of times their name occurs in said sheet. Their names will also only be in one column so the formula does not need to scan the entire sheet.

Is there a way for Excel to do this for me?

Example of my sheet

  • Welcome to Super User! You have a column with data which contains employee names, and you want to individually count these occurrences in another column, is that correct? And you don't want to set up the individual employee names manually? – bertieb Mar 6 '18 at 17:39
  • Sorry that I wasn't clear. I would like excel to figure out which names appear and count those names. I do not want to have to type all 300 employees names into a countif or other function. – Forrest Bowman Mar 6 '18 at 17:46
  • Bertieb, yes that is exactly what I am wanting. Thanks for your time. – Forrest Bowman Mar 6 '18 at 17:48
  • I had added an answer (with example data) based on what I think you are asking; if I'm wrong please let me know :) – bertieb Mar 6 '18 at 18:11
  • If the names are all in a single column, you can just use Pivot Table. – Ron Rosenfeld Mar 7 '18 at 21:12
1

Extract unique and count employee names

If you want both the employee names in a new column, and a count of their occurrences, you can use a combination of an advanced filter and COUNTIF.

  1. Filter

    Select the range in your data column with names that you want to get the names and count from, select the Data tab, and then Advanced.

    Select Copy to another location, and choose that location (in Copy to) by either filling it in or using the selector. Make sure Unique records only is selected with a check.

    That will give you the unique names from the column.

    Illustrated:

    animated gif describing how to filter

  2. Count

    From here you can use COUNTIF, using the unique names for reference.

    For example, if the name data is in column A, the unique names start in column C (from C2): =COUNTIF(A:A, C2), and fill down with eg Ctrl+D

    That will give you the count of names from the column.

    Illustrated:

    animated gif showing counts of names

0

This shouldn't take long. If the names are in column A use the following countif:

=COUNTIF(A:A,A2)

This counts all appearances of the name in A2 anywhere in column A. Since you are not typing the name but instead are referencing the cell it resides in you can just drag the formula down.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.