How to Count Names in Excel: Formulas and Examples

Formulas & functions · Updated November 20, 2026

To count how often a particular name appears in Excel, use COUNTIF: with names in A2:A100 and the name to look for in D2, enter =COUNTIF($A$2:$A$100,D2). To count every filled name cell, use COUNTA; to count distinct names, use a dynamic-array formula. The right method depends on whether you mean occurrences, nonblank cells, or different names.

What You’ll Need

  • A Microsoft Excel workbook, using Excel for Microsoft 365 on Windows for the steps below.
  • A column or range containing the names you want to count. The examples use A2:A100, with a header in A1.
  • For a reusable exact-name count, a cell where you can enter the name to find, such as D2.

If you use Excel for the web, the formulas below work there too. On a Mac, the formula syntax is the same, although ribbon labels and some keyboard shortcuts can differ slightly.

Step-by-Step

  1. Decide what “count names” means for your task. If you want to know how many times one particular name occurs, use an exact-match count. If you need the total number of filled cells, count nonblank entries. If you need the number of different people, count unique names instead. These answers can be different: a list containing Ana, Ben, Ana has three entries but only two distinct names.
  2. Check the name list and identify its range. Click a name cell and note the column letter and row numbers. In the examples, A1 is a heading and the names run from A2 through A100. Adjust the references to fit your actual data. Avoid including a heading in the range, because Excel may count the heading as if it were another name.
  3. Count one exact name with COUNTIF. With names in A2:A100 and the name to find typed in D2, enter =COUNTIF($A$2:$A$100,D2) in E2. Press Enter. The dollar signs keep the list range fixed if you copy the formula down, while D2 can change to D3, D4, and so on. If Ana appears four times in the list and D2 contains Ana, the result is 4. COUNTIF is not case-sensitive, so Ana and ANA are treated as the same match.
  4. Use a name written directly in the formula if you prefer. With the names still in A2:A100, to count cells whose full contents match Ana, enter =COUNTIF($A$2:$A$100,"Ana"). The quotation marks tell Excel that Ana is text. This is handy for a quick check, but a separate criteria cell is easier to update and reuse. COUNTIF does not distinguish uppercase from lowercase, and a name must match the cell content apart from that case difference.
  5. Count partial matches when names appear inside longer text. If A2:A100 contains entries such as Ana Rivera, Ana Chen, and Ben Anais, and you want cells containing the text Ana anywhere, enter =COUNTIF($A$2:$A$100,"*Ana*"). The asterisks are wildcards meaning “any number of characters,” including none. This returns 3 for those example entries. It also matches longer words containing Ana, so it is not the right choice if you mean only the separate first name Ana. COUNTIF wildcard matching is also case-insensitive.
  6. Count all cells that contain an entry. With names in A2:A100, enter =COUNTA(A2:A100) to count non-empty cells. If 85 of the 99 cells have names and the rest are truly blank, the result is 85. This counts any nonblank content, not just valid names; a note, number, or error in the range is included too. A formula that displays an empty string can also be counted by COUNTA, even though the cell looks blank.
  7. Count distinct names rather than repeat entries. With names in A2:A100 in Excel for Microsoft 365, enter =IFERROR(COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). FILTER removes empty cells, UNIQUE returns one copy of each distinct entry, and COUNTA counts those results. If the only entries are Ana, Ben, Ana, and an empty cell, the answer is 2. This formula uses UNIQUE and FILTER, which are available in Microsoft 365 and Excel 2021 or later. IFERROR makes an entirely empty range return 0 instead of an error.
  8. Copy the formula or check a summary with a PivotTable. To count several names, list each name in D2:D10, enter the COUNTIF formula in E2, then fill it down using the fill handle at the cell’s lower-right corner. Alternatively, select the list and choose Insert > PivotTable. Put the Name field in Rows and add the same field to Values; if Excel summarizes it as Sum, open Value Field Settings and choose Count. A PivotTable is useful when you want a refreshable summary of many names.
  9. Verify the result against a small sample. Filter the name column or use Find to check a few occurrences. If you expect a total count, compare it with COUNTA; if you expect different people, compare it with the unique-name count. Save the workbook after confirming the range covers all the records you intended to include.

To fill a formula down on Windows, select the formula cell and drag its fill handle, or select the destination cells and press Ctrl+D. On Mac, use Command+D to fill down. If your Excel settings use semicolons rather than commas between formula arguments, use the separator Excel expects for your region; the examples show standard comma-separated syntax.

Common Mistakes

  • Counting the heading as a name. A range such as A1:A100 includes the column title. Start at A2 when A1 is a header, or select the table’s data column rather than the whole worksheet column.
  • Confusing occurrences with unique people. COUNTIF reports how many matches there are, including repeats. It does not tell you how many different names are represented. Use the distinct-name formula when duplicates should count only once.
  • Expecting COUNTIF to match only part of a name without wildcards. A criterion of Ana matches the cell Ana, not Ana Rivera. Add wildcards when you intentionally want a partial match, and review the results because the text can occur inside another word.
  • Overlooking extra spaces or inconsistent spelling. Ana and Ana followed by a space may not behave as expected in exact-match counts. Similarly, Ana Rivera and Ana R. are different text values. Use a consistent spelling and clean imported data before treating the count as authoritative.
  • Assuming COUNTA counts only names. COUNTA counts every non-empty cell, including numbers, notes, and errors. It can also count cells containing formulas that return an empty string. Inspect the data or use a more specific condition if the column contains mixed content.
  • Using the wrong range or copying a formula with a moving range. If you copy a formula down and its source range shifts, some rows may be left out. The dollar signs in $A$2:$A$100 lock the range while allowing the criteria reference to move.

Tips

Turn a simple list into an Excel Table by selecting it and choosing Insert > Table. Tables expand when you add rows, which helps keep summaries current. If you use a table, structured references can make formulas easier to understand, though the cell-reference examples in this guide are simpler for a first count.

For a reusable report, put the names to check in one column and the counts beside them. Keep the source range absolute, as in $A$2:$A$100, and use a relative criteria reference such as D2. Then fill down. This gives each listed name its own count without rewriting the formula.

For a very large list, consider removing obvious blank rows and standardizing spelling before counting. You can use Excel’s Find and Replace to correct a known repeated typo, but check the replacements carefully so a short name is not changed inside a longer, unrelated name. Keep an unchanged copy of imported source data when accuracy matters.

COUNTIF and UNIQUE compare text without regard to letter case for these ordinary name-counting tasks. If your process genuinely treats Ana and ANA as different identities, a standard COUNTIF result will not meet that requirement; you would need a case-sensitive approach, such as a carefully designed formula using EXACT, and should test it on representative data before relying on it.

Troubleshooting

  • The result is zero, but the name appears in the list: Check for leading or trailing spaces, spelling differences, or a range that does not include the row. Click the source cell and inspect the formula bar. If the criterion is in D2, make sure D2 contains the value you intend to count, not a different name or an accidental space.
  • The result is higher than expected: If you used wildcards, the text may be matching inside longer names or other words. Switch to an exact COUNTIF criterion to count whole-cell matches. Also check whether repeated entries are meant to count, and whether the range includes a heading or extra rows.
  • The unique-name formula returns an error: Confirm that your Excel version supports UNIQUE and FILTER, and check that the formula was entered with balanced parentheses and quotation marks. In Microsoft 365, the formula should spill its intermediate array automatically. If your regional settings require semicolons, replace the argument separators accordingly.
  • The count does not change after adding names: Check that the new rows fall inside the range used by the formula. A fixed range ending at A100 will not include a new entry in A101. Expand the range, or convert the list to a Table and base your workflow on its growing data area.
  • Excel displays the formula as text: Change the result cell’s format to General, then edit the formula and press Enter. Also check that the entry begins with an equals sign and that there is no apostrophe before it. A cell formatted as Text may show the formula instead of calculating it.
  • You see a formula warning or unexpected separator error: Use straight quotation marks and make sure every opening parenthesis has a closing one. If your locale uses semicolons as argument separators, adapt the formula to that setting. Do not replace commas inside a quoted name unless they are part of the name itself.

Frequently Asked Questions

How do I count how many times each name appears?
Put each name you want to summarize in a separate cell, such as D2:D10. With the source names in A2:A100 and the name to count in D2, enter =COUNTIF($A$2:$A$100,D2) in E2 and fill it down. Each row returns the occurrence count for the corresponding name.

Does COUNTIF distinguish between uppercase and lowercase names?
No. COUNTIF is case-insensitive, so Ana, ANA, and ana match the same criterion. It can distinguish different characters and spelling, but not ordinary differences in capitalization.

How can I count names while ignoring blank cells?
For the number of filled cells, use COUNTA on the name range. For distinct names while excluding blanks in Microsoft 365 or Excel 2021 and later, use the UNIQUE and FILTER formula above. Remember that COUNTA also counts non-name content and formulas that display an empty string.

Can I count a name in multiple columns?
Yes. If names are in A2:B100, you can give COUNTIF a two-column range. With the name to find in D2, use =COUNTIF($A$2:$B$100,D2). This counts matching cells across both columns, not rows where the name appears at least once; a row with the name in both columns contributes two.

Can I count names that contain a particular word or fragment?
Yes. Use asterisks around the text in a COUNTIF criterion, such as =COUNTIF($A$2:$A$100,"*Rivera*") when names are in A2:A100. It counts cells containing Rivera anywhere. Because this is a partial match, it can also find that fragment within a longer string.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.