Excel Xpert · Bonus Lesson
One function does this, and it is COUNTIF. The formula takes about ten seconds to write. The reason people search for it twice is everything around it: getting a count beside every name instead of one at a time, counting partial matches, and working out why a name that is obviously sitting in the list comes back as zero. All of it is below.
Bonus Lesson. The video shows how to use COUNTIF to count how many times a name appears in a list, and how to set the formula up so it counts every name at once rather than checking one name at a time. The written steps below cover the same method in detail, plus wildcards for partial matches, the trailing space problem that makes counts come back as zero, and how to build a clean unique list with totals beside it.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
COUNTIF takes two things: a range to look through and a criteria to look for. It walks every cell in the range, compares it to the criteria, and returns how many matched. That is the entire function.
So =COUNTIF(A2:A200,"Smith") reads as: in cells A2 through A200, how many say Smith. The quotation marks are there because Smith is literal text. Point the criteria at a cell instead and the quotation marks disappear, because a cell reference is not text, it is a location.
That distinction matters more than it looks, and it is the difference between a formula you rewrite for every name and one you write once. =COUNTIF($A$2:$A$200,A2) asks how many times the name in A2 appears. Fill it down and each row asks about its own name, so you get a count beside every entry in a single action.
The criteria argument is also more capable than most people use it for. It accepts wildcards, so an asterisk stands for any run of characters and a question mark for exactly one. That turns COUNTIF from an exact match tool into a partial match tool, which is how you count every name containing "ER" or every entry starting with "Mc".
What COUNTIF will not do is worth knowing upfront. It is not case sensitive, so Smith and SMITH are the same to it. It ignores filtering, so hidden rows still count. And it tests one condition only, so counting a name in one column while checking a date in another is COUNTIFS, its plural sibling.
Six steps. Steps three and four are what turn a one-off answer into a working column.
Note where the names start and end, for example A2:A200. Start at the first name, not the header. Including a header called "Name" rarely matters, but including a totals row or a stray note at the bottom quietly inflates your counts.
If the data is in a proper Excel table, use the column reference instead, such as Table1[Name]. It expands automatically as rows are added, which saves editing every formula later.
Selecting an entire column with A:A also works and is safe here, since COUNTIF handles blanks fine. It is only worth avoiding when you have dozens of these formulas on a large sheet, where the extra work starts to show.
In an empty cell, type =COUNTIF(A2:A200,"Smith"). Range first, criteria second. The result is how many cells in that range contain exactly Smith.
The quotation marks are required around typed text and are the most common syntax mistake here. Leaving them off makes Excel look for a named range called Smith, which does not exist, so you get #NAME? rather than a count.
This version answers one question about one name. It is the right tool when you genuinely only need one number, and the wrong one the moment there is a second name to check.
Replace the typed name with a cell reference: =COUNTIF(A2:A200,C2), where C2 holds the name you want counted. No quotation marks, because C2 is a location rather than a piece of text.
Now the formula is reusable. Type a different name into C2 and the count updates instantly, which beats editing a formula every time somebody asks about a different person.
This is also what makes the formula work as a lookup box on a dashboard: one input cell, one count, no formula editing by whoever is using the sheet.
Write it as =COUNTIF($A$2:$A$200,A2) in the row beside your first name, then fill down the whole list. Every row now shows how many times its own name appears.
The dollar signs matter and are the reason this either works or produces nonsense. The range is locked so it stays pointing at the full list as the formula moves down. The criteria is relative so it shifts with each row and asks about that row's name.
Forget the dollar signs and the range slides down with the formula, so by row 100 it is only looking at the bottom half of your data and every count is too low. This one detail causes more wrong COUNTIF results than any other.
An asterisk stands for any number of characters, a question mark for exactly one. So =COUNTIF(A2:A200,"*er*") counts every name with ER anywhere in it, and =COUNTIF(A2:A200,"Mc*") counts everything starting with Mc.
To combine wildcards with a cell reference, join them: =COUNTIF($A$2:$A$200,"*"&C2&"*"). The ampersands glue the asterisks onto whatever C2 contains, so the input cell stays a plain name.
If you need to count a literal asterisk or question mark rather than use it as a wildcard, put a tilde in front of it, as in ~*. This comes up more often than expected with product codes.
When a name is plainly in the list but COUNTIF says zero, the cell almost always contains something invisible. A trailing space is the usual culprit, and "Smith " with a space is genuinely not equal to "Smith".
Confirm it with =LEN(A2). If a five letter name reports 6, there is an extra character. Fix the whole column with =TRIM(A2) for spaces, or =TRIM(CLEAN(A2)) when the data was pasted from a website or an exported system that brought non printing characters along.
Paste the cleaned values back over the originals as values, then re-run the count. Doing the cleanup once at the source beats wrapping TRIM around every formula that touches the column afterwards.
| Step | What You Type | What It Gives You |
|---|---|---|
| 1. Range | A2:A200 or Table1[Name] | The list to search, header excluded |
| 2. One name, typed | =COUNTIF(A2:A200,"Smith") | How many times Smith appears |
| 3. One name, from a cell | =COUNTIF(A2:A200,C2) | Change C2, the count changes |
| 4. Every name at once | =COUNTIF($A$2:$A$200,A2) | Fill down, each row counts its own name |
| 4. Same, unlocked | =COUNTIF(A2:A200,A2) | The usual bug, counts drop as you go down |
| 5. Contains a fragment | =COUNTIF(A2:A200,"*er*") | Every name with ER anywhere in it |
| 5. Wildcard from a cell | =COUNTIF($A$2:$A$200,"*"&C2&"*") | Partial match on a typed input |
| 6. Diagnose a zero | =LEN(A2) | Reveals a trailing space or hidden character |
| 6. Fix it | =TRIM(CLEAN(A2)) | Strips spaces and pasted junk |
The two rows under step 4 are the same formula with and without dollar signs, and that single difference is the most common reason a filled down COUNTIF column returns numbers that get smaller toward the bottom. If your counts look like they are decaying, check the locking first.
A count beside every row is useful, and a unique list is usually what you actually want to show someone.
=UNIQUE(A2:A200) spills the distinct names into a column. Put =COUNTIF($A$2:$A$200,E2) beside the first spilled name and fill down, and you have every name once with its total. Requires Microsoft 365.
Drop the name field into both Rows and Values, and Excel produces a count per name automatically. This is faster than formulas for a one-off answer and updates on refresh rather than instantly.
With counts in a helper column, sort the block by that column descending to see the most frequent names first. Wrap the COUNTIF in SORT for a version that reorders itself as the data changes.
Conditional formatting with the rule =COUNTIF($A$2:$A$200,A2)>1 colors every name appearing more than once, which is often more useful than the number when you are cleaning a list.
Four situations that need a different function.
To count a name only within a date range or only for one region, COUNTIFS takes range and criteria pairs, as in =COUNTIFS(A2:A200,"Smith",B2:B200,"West"). All conditions must be true for a row to count.
COUNTIF sees Smith and SMITH as identical. =SUMPRODUCT(--EXACT(A2:A200,"Smith")) counts only exact case matches, which matters for codes and IDs far more than for names.
COUNTIF counts hidden rows too, which surprises people constantly. SUBTOTAL respects filtering but takes no criteria, so the combined approach is a SUMPRODUCT built on SUBTOTAL, or simply reading the status bar count with the filter applied.
COUNTIF cannot span sheets in one reference. Add one COUNTIF per sheet together, or better, consolidate the data onto one sheet first with VSTACK or Power Query and count once.
Without dollar signs the range slides down with the formula, so counts shrink toward the bottom of the list. If your numbers look like they are decaying, this is why.
A name with an invisible space at the end genuinely does not match. Check with LEN before rewriting a formula that was correct all along.
Typing Smith without quotes makes Excel look for a named range and return #NAME?. Cell references need no quotes, typed text always does.
Both quietly add to the count. Start the range at the first real name and stop at the last one, or use a table column reference that handles the boundaries for you.
It counts hidden rows exactly the same as visible ones. Filtering the sheet does not change a COUNTIF result, which catches people out on filtered reports.
An asterisk left in from an earlier attempt makes the count far too high, because it now matches anything containing the text rather than equalling it.
Counting is what you do when the data cannot answer for itself.
Free 14-day trial. No credit card required.
Use COUNTIF with the list as the range and the name as the criteria, such as COUNTIF of A2 through A200 counting Smith. It returns how many cells in that range match.
Put a COUNTIF in a helper column that points at each row's own name, locking the range but not the criteria, then fill it down so every row shows its own name's total.
Almost always a trailing space or a non breaking character from a paste. TRIM and CLEAN fix it, and comparing the length of the cell with LEN confirms it before you go hunting elsewhere.
No, COUNTIF treats Smith and SMITH as the same value. For a case sensitive count, use SUMPRODUCT with EXACT instead.
Use wildcards in the criteria. An asterisk stands for any number of characters, so criteria of asterisk ER asterisk counts every name containing ER anywhere in it.
COUNTIF tests one condition against one range. COUNTIFS tests several conditions across several ranges at once and counts only rows where all of them are true.
Use UNIQUE to spill the distinct names into a column, then a COUNTIF beside them referencing that spilled list. A pivot table with the name in both Rows and Values does the same thing.
COUNTIF accepts a multi column range in one go. Across sheets it cannot span them, so add one COUNTIF per sheet together, or consolidate the data first.
COUNTIF ignores filtering and counts hidden rows too. SUBTOTAL counts only visible rows but takes no criteria, so combining SUMPRODUCT with SUBTOTAL is the usual route.
Use conditional formatting with a formula rule based on COUNTIF being greater than one, or use the built in Duplicate Values rule under Highlight Cells Rules.
Yes, and it is better. Point the criteria at a cell so changing that cell changes the count, and no quotation marks are needed around a cell reference.
Either the criteria contains a wildcard character matching more than intended, or the range includes the header row or a totals row. Check both before assuming the data is wrong.
Every business starts with a spreadsheet. Updoot is where you scale past it, with totals that are already current when you open them.
Start Your Free Trial →