To count how often each individual name appears, list the names in one column, create a unique-name list, and count each name against the original data. In Excel for Microsoft 365, with names in A2:A100, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"","")) in D2. Then, with the unique names spilling from D2, enter =COUNTIF($A$2:$A$100,D2) in E2 and fill it down. The result is a two-column summary showing each name and its number of occurrences.
What You’ll Need
- An Excel workbook containing the names you want to count. This guide assumes the names are in one column, with a single name in each cell.
- Microsoft Excel for Microsoft 365 or Excel 2021 for the automatic unique-name list. The
UNIQUEandFILTERfunctions are available in these versions. If you use an older Excel version, see the alternative in Step 3. - A blank area for the summary results. Keep it separate from the source names so the formulas do not overwrite your data.
Step-by-Step
- Check and organize the source list. Put one full name in each cell of a single column, with a heading such as Name in the first row. For example, the heading could be in A1 and the actual names in A2:A100. The steps below use that layout; if your names are in another column or cover a different number of rows, substitute your actual range in the formulas. Avoid including the heading in the range, because Excel would count the heading as if it were a name. If a cell contains several names separated by commas, split those values into separate cells first; otherwise Excel will count the whole cell as one entry.
- Choose where the summary will go. Click an empty cell well to the right of the source list, such as D1, and type Name. In E1, type Count. These headings make the output easy to read and help distinguish the list of distinct people from the tally. Make sure the cells below D1 and E1 are empty, because the unique-name formula needs room to spill its results down the column.
- Generate a list of individual names. With names in A2:A100, enter
=UNIQUE(FILTER(A2:A100,A2:A100<>"",""))in D2. Press Enter. Excel returns each nonblank name once, in the order that name first appears in the source list. FILTER removes blank cells before UNIQUE builds the list, while its final empty-string argument prevents an error if the source range contains no names. If you add more names below row 100 later, update the range or convert the source data to an Excel table so the range can expand more easily. - Count the occurrences of the first name. With the original names in A2:A100 and the unique-name list beginning in D2, enter
=COUNTIF($A$2:$A$100,D2)in E2 and press Enter. COUNTIF checks the original range and returns how many cells match the name in D2. The dollar signs keep the source range fixed when you copy the formula, while D2 changes to D3, D4, and so on for the names on subsequent rows. - Fill the count formula down the list. Select E2 and drag the small square at the bottom-right corner of the selection down alongside the last name in column D. You can also copy E2, select the cells below it that need counts, and paste. The count next to each name should now show its frequency in the original list. Check that the last formula row lines up with the last name in the unique-name list; if the list changes later, extend the count formulas as needed.
- Check the result with a small example. If A2:A7 contains Ana, Ben, Ana, Mei, Ben, Ana, the summary should show Ana with 3, Ben with 2, and Mei with 1. Those counts add up to 6, which is the number of nonblank entries in the example. A matching total is a useful quick check that every source entry has been accounted for. It does not prove that spelling differences are correct, so inspect names that appear unexpectedly as separate rows.
- Count just one specific name if that is all you need. With names in A2:A100 and the name you want to check typed in D2, enter
=COUNTIF($A$2:$A$100,D2)in E2. This returns one count without creating a unique-name list. You can type the name directly into the formula criteria instead, but using a cell makes it easy to change the person being counted without editing the formula. - Use a manual unique list in older Excel. If your version does not have UNIQUE and FILTER, copy the source names to an empty column, then select that copied list and choose Data > Remove Duplicates. Confirm that the correct column is selected and confirm the prompt. This removes repeated values from the copy, so do not run it on your original list unless you intend to delete duplicates there. Then enter the COUNTIF formula beside the resulting names and fill it down. The Remove Duplicates command changes the selected cells; it is not a live formula, so repeat the process when the source list changes.
Common Mistakes
Counting the unique list instead of the source list: COUNTIF must check the original column, A2:A100 in this example. If you point it at the unique list in column D, each name will normally count as 1 because each appears there only once. Keep the source range as the first argument and the current unique name as the criterion.
Including a heading or unrelated rows: If the range begins at A1, Excel may include the word Name as an entry. If it extends into other data, those cells can distort the results. Use a range that contains only name records, and keep the start and end rows consistent in both formulas.
Assuming similar-looking names are identical: Excel treats Ana, Ana Maria, and Ana with an extra space as different text values. A spelling variation, middle initial, or trailing space can therefore create another summary row. Review the source data before assuming that separate rows represent separate people.
Using COUNTA for individual tallies: COUNTA can tell you how many nonempty cells are in a range, but it does not separate the total by name. Use COUNTIF for a particular name or use the two-column method for all names.
Overwriting the source while removing duplicates: Remove Duplicates permanently changes the selected cells. Make a copy of the source column first and run the command on that copy if you need to preserve every original record.
Tips
Turn the source range into an Excel table if names will be added regularly. Select the data, including its heading, and choose Insert > Table, making sure the table has headers. Tables expand as you add records, which reduces the chance of leaving new names outside a fixed range. If you use a table, use its name column as the source range in your formulas; the table’s name and column heading must match your workbook.
Sort the summary after the counts are calculated if you want to find the most frequent names quickly. Select the summary and choose Data > Sort, then sort by the Count column from largest to smallest. Keep the name and count columns together when sorting so each count stays beside the correct person. You can also use Home > Sort & Filter, but check that Excel expands the selection to include both columns when it asks.
Remember that COUNTIF is not case-sensitive. It normally treats Alex and ALEX as the same name. It also counts exact text matches by default, but a cell with an extra space or an additional character is not an exact match. If the distinction between uppercase and lowercase letters matters for your task, COUNTIF alone is not enough; you would need a case-sensitive approach.
For a quick total of all nonblank name entries, compare the sum of the individual counts with the number of filled source cells. With names in A2:A100, =COUNTA(A2:A100) counts nonempty cells. This total includes repeated names, which is exactly what you want when checking that the individual frequencies account for every filled cell. It does not tell you how many different people are represented.
Troubleshooting
The UNIQUE formula shows #SPILL!: Excel cannot place the results because one or more cells in its output area are occupied, merged, or otherwise blocking the spill range. Clear the cells below D2 where the results need to appear, unmerge any obstructing cells, and re-enter the formula. Check the adjacent count column too, so the two output columns have room.
The formula displays as text or does not calculate: Make sure the cell is not formatted as Text. Change the format to General, then select the cell, press F2, and press Enter to make Excel read the entry as a formula. Confirm that the formula begins with an equals sign and that there is no apostrophe before it. Excel for the web and Excel for Mac support these functions in current Microsoft 365 versions, though the ribbon layout can differ slightly.
Names that look the same get separate counts: Inspect the original cells for leading or trailing spaces, inconsistent punctuation, or spelling differences. You can create a cleaned helper column before making the unique list. With the original name in A2, enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in B2, then fill it down and use the cleaned values in column B for both the unique list and COUNTIF. This removes many extra spaces and nonprinting characters, including common nonbreaking spaces, but review the cleaned names because normalization can change the text.
A name’s count is zero or unexpectedly low: Check that the COUNTIF range includes every source row and that the criterion points to the intended name in column D. Look for hidden spaces or a different spelling in the original cells. Also make sure your formula uses commas between arguments, as shown; some regional settings use semicolons instead, in which case Excel may adjust separators according to those settings.
The results do not update when you add names: A fixed range ending at A100 will not include entries entered in A101 or below. Extend the source range in both formulas, or use an Excel table and formulas that refer to its name column. If calculation is set to Manual, choose Formulas > Calculation Options > Automatic, or press F9 to recalculate.
Frequently Asked Questions
How do I count one name in Excel?
With names in A2:A100 and the name to check in D2, enter =COUNTIF($A$2:$A$100,D2) in E2. The result is the number of matching cells in the source range. Change D2 to check another name.
How do I count every different name automatically?
Use UNIQUE with FILTER to create a list of nonblank names, then place COUNTIF beside the list and fill it down. UNIQUE and FILTER are available in Microsoft 365 and Excel 2021. If your Excel version lacks them, copy the names to a separate area, remove duplicates from that copy, and count the resulting list.
Does COUNTIF distinguish between uppercase and lowercase?
No. COUNTIF is case-insensitive, so it counts Alex and ALEX together. If uppercase and lowercase should represent different names, use a case-sensitive counting method rather than the basic COUNTIF approach.
How can I count names while ignoring blank cells?
The FILTER portion of the unique-list formula excludes empty cells before Excel creates the summary. COUNTIF counts matches for the listed names, so blank cells do not appear as a person in the result. Make sure your source range does not contain cells with spaces only, because those are not truly empty.
Can I count names from more than one column?
Yes, but first decide whether the cells form one combined list. You can place the values into one column and apply the steps above. In Microsoft 365 or Excel 2024, VSTACK can combine ranges, but if you use it, check that the ranges have compatible layouts and adjust the formulas to use the combined list. For a one-time tally, copying the columns beneath one another may be simpler.
Why are two spellings of the same person’s name listed separately?
Excel counts the text it finds; it cannot know that two different spellings refer to one person. Correct the source entries or use a reviewed cleaned-name column that standardizes them before making the summary. Do not automatically merge similar-looking names if they could belong to different people.
Related
- Compare Two Lists of Names in Excel: Find Matches
- How to Compare a List of Names in Excel: 10 Methods
- Compare Actual vs Budget in Excel: Step-by-Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IFERROR with VLOOKUP in Excel: Hide Lookup ErrorsYes. With lookup IDs in A2:A100, return values in C2:C100, and the value to search for in E2,…
- Excel FV Formula: How to Calculate Future Value and GrowthIn Excel for Microsoft 365, use the FV function to calculate the future value of regular payments or…
- Excel Function to Remove Text from a StringExcel does not have one universal “remove text” function: use SUBSTITUTE to remove specified characters or words, REPLACE…
- Count a Particular Word in Excel: Formulas That WorkTo count every occurrence of a term across a range in Excel, put the term in D1 and…
- Smartsheet SUMIF Examples for Everyday ReportsIn Smartsheet, SUMIF adds values in a sum range only when corresponding cells meet one condition. For example,…
- Cannot Filter in Excel? Causes, Checks, and FixesYes—Excel can filter both ordinary data ranges and tables. In Microsoft 365 for Windows, select a cell in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.