WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android


How to count names in Excel without duplicates?

July 22, 2022

Counting names in excel is an essential skill that you need as a student or business person. Accurate counting is crucial in every industry, whether it's sales consultancy, accounting, or mission control. A name list can either be useful for a specific function like scheduling or be updated regularly for a large number of people.

Counting names in Excel is often one of the most time-consuming tasks and one of those tasks that you will encounter every other day using excel. In this article, we'll show you how to count names in excel in an easy and simple way , there are different methods to do that, let us take you through each one by one.

How to count names in excel using the COUNTA function

The COUNTA function is one of the most widely used functions in Excel. It counts how many times a specified value appears in a list. This can be useful for counting names, for example, in a spreadsheet that includes customer names or employee names.

The syntax for the COUNTA function is:


The COUNTA function counts the number of rows in a specified range. This can be useful to find out how many names and addresses are in a list. To use the COUNT function, you must specify a range that contains at least one cell and contains at least one value.

1.Take an example at the list of names that we want to count and we want our answer in D3 cell.

2.We will select the COUNTA function under statistical formulas provided in formulas tab.

3.Here inside the value1, we enter the range of data we want to count which in our case is from A2 to A7.

4.The result will come in our desired tab i-e D3.

How to count specific names in excel

Sometimes you may be looking for how many times a specific name comes in some list, for this purpose you have to use the COUNTIF function, the syntax of this function is

COUNTIF (Range, match case)
1.Let's suppose we are looking at a list of how many times John has been awarded the “Employee of the month award in the following list.

2.Select the range of data in the column and apply the COUNTIF function to get the answer.

3.The answer we get here is 2, which means Jake occurs 2 times in our data.

How to count names in excel using the SUMPRODUCT function

Sometimes, you have to take into account both upper- and lower-case characters while counting the names, for this, we use the SUMPRODUCT function, which is used when data is case sensitive like in the case given below.

1.We can see that in our Data Two Henry are present but our concern is with a single one only, we will apply the function as.

2.Inside the formula bar, we have entered the exact value we want to match with and the range of our data.

The answer comes out as 1 as expected.

3.And that's the SUMPRODUCT method for counting names in excel.

Did you learn about how to count names in excel using different methods You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience. 

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.