The COUNT Function in Excel (with COUNTA, COUNTIF, COUNTIFS & Distinct Counts)

You'd think counting things in Excel would be simple. Highlight a range, glance at the status bar, done. And honestly, for a quick check it is. But the moment you need to count only the numbers, or only the non-blank cells, or only the rows that match some condition — that's when you reach for the COUNT family of functions. Five core functions, each with its own job, and once you know which one does what, you'll stop second-guessing yourself every time the boss asks "how many?"

This guide walks through the whole family. The plain COUNT for numbers. COUNTA for anything that isn't empty. COUNTBLANK for the gaps. COUNTIF when you've got one rule to check. COUNTIFS when you've got two or three (or twenty) rules stacked together. Plus the tricky stuff — counting distinct values without a helper column, counting how many times a specific word shows up, counting months between two dates, using wildcards to match partial text. All of it.

What you won't get here is fluff. Each function gets the syntax, a real example you can copy straight into a sheet, and the gotchas that trip people up. By the end you'll know exactly which COUNT to reach for, whether you're tallying invoices, auditing a data dump, or building a dashboard that updates as the spreadsheet changes underneath it.

Start with the basics. The COUNT function counts cells in a range that contain numbers. That's it. Text is ignored. Logical values get ignored. Empty cells get ignored. Only numeric values get tallied.

Syntax — =COUNT(value1, [value2], ...). You can pass a single range, multiple ranges, or even individual values. Most often you'll just hand it a column or a block of cells.

A worked example. Suppose A2:A20 holds a mix of numbers, blank cells, and a few text entries like "TBD" or "N/A". The formula =COUNT(A2:A20) returns only the numeric count — say 14 if there are 14 numbers in the range. The text and blanks don't count. Dates count too, because Excel stores dates as serial numbers under the hood.

One thing people miss — numbers stored as text don't count. If a cell holds '42 with that leading apostrophe (or was imported from a CSV with the column formatted as text), Excel sees it as a string and COUNT skips it. You'll know because the cell-value tooltip will show the green triangle warning in the corner. To fix, convert to numbers using Paste Special multiply by 1, or wrap your formula with VALUE.

COUNT is useful but narrow. The moment you need to count text entries or non-blank cells, you've moved into COUNTA territory.

COUNTA is the slightly bigger sibling. It counts every cell in a range that isn't empty — numbers, text, logical values, error values, even formulas that return an empty string. If there's anything at all in the cell, COUNTA counts it.

Syntax — identical. =COUNTA(value1, [value2], ...). Pass it a range and it returns the non-blank count.

Real example. A2:A100 holds a contact list with names, blank rows where you've deleted entries, and a few cells with phone numbers. =COUNTA(A2:A100) returns the total number of populated cells — your live contact count. Add or remove a name and the number updates automatically.

One subtle gotcha — formulas that return an empty string like =IF(A2=1,"X","") still count as non-blank to COUNTA. The cell contains a formula, the formula returns text (even if that text is zero characters long), and COUNTA sees something. If you want truly empty cells to count as empty, you'll need a different approach — either use COUNTIF to count only cells matching a specific pattern, or use SUMPRODUCT to filter the empty strings out yourself.

COUNTA is the right tool when you're asking "how many entries do I have?" rather than "how many numbers?" — list lengths, populated row counts, response counts on a survey sheet, that kind of thing.

Sometimes you need the opposite. Not how many cells have data, but how many are empty. That's COUNTBLANK.

Syntax — =COUNTBLANK(range). One argument only. A single range.

If A2:A100 is a survey response column and you want to know how many people skipped the question, =COUNTBLANK(A2:A100) returns the missing count. Pair it with COUNTA on the same range and you've got the full picture — total entries plus total gaps.

Watch out for the same formula-returns-empty-string trap. COUNTBLANK does count cells that contain a formula returning "" as blank. So if you've got =IF(A2=1,"X","") filling down a column and most rows return the empty string, COUNTBLANK will count those as blank even though there's technically a formula in the cell. Helpful for some use cases, annoying for others. If you want only truly empty cells (no formula at all), use =ROWS(range)-COUNTA(range) as a workaround.

COUNTBLANK shines for data quality audits. Run it across each column of an import to see where the holes are before you build dashboards on top of incomplete data.

Here's where things get interesting. COUNTIF counts only the cells in a range that meet a single condition you specify. Suddenly you can ask Excel real questions — how many orders are over $500? How many students passed? How many rows say "Active" in the status column?

Syntax — =COUNTIF(range, criteria). Two arguments. The range to look in, and the criteria to match.

The criteria is the interesting part. Numbers and text both work, but you wrap comparisons and text in quotes. Examples that hit the most common cases:

Count exact text match. Count rows where column B says "Active": =COUNTIF(B2:B100,"Active").

Count numbers greater than a threshold. Orders over 500: =COUNTIF(A2:A100,">500"). Notice the operator goes inside the quotes.

Count cells matching another cell's value. Use the cell reference directly: =COUNTIF(B2:B100,D2). No quotes around the reference.

Count cells greater than another cell's value. Combine the operator and reference with concatenation: =COUNTIF(A2:A100,">"&D2).

Text comparisons are case-insensitive by default. "active", "Active", and "ACTIVE" all match the same. If you need case-sensitive counting, you'll have to combine SUMPRODUCT with EXACT instead — there's no clean COUNTIF alternative.

Wildcards are where COUNTIF really earns its keep. Two characters do the heavy lifting — the asterisk * matches any number of characters (including zero), and the question mark ? matches exactly one character.

Count cells containing a specific word anywhere in the text. Counts rows where column B contains "Excel" anywhere — at the start, middle, or end: =COUNTIF(B2:B100,"*Excel*").

Count cells starting with a specific letter. Names starting with S: =COUNTIF(A2:A100,"S*").

Count cells ending with a specific suffix. Emails ending in @gmail.com: =COUNTIF(B2:B100,"*@gmail.com").

Count cells of an exact length using question marks. Five-character postal codes: =COUNTIF(C2:C100,"?????").

If you need to match a literal asterisk or question mark — say you're counting price entries that include the character * — prefix it with a tilde: =COUNTIF(A2:A100,"~*"). Same goes for the question mark — ~? matches a literal question mark.

Wildcards only work with text. If the range contains numbers, the wildcard match returns zero. Convert numbers to text first using TEXT() if you really need to wildcard-match digits.

One condition is fine for simple questions. But what about counting rows where the date is in the last 30 days and the status is Open and the amount is over $1,000? That's where COUNTIFS takes over.

Syntax — =COUNTIFS(range1, criteria1, range2, criteria2, ...). You pair each range with its own criteria, and Excel returns the count of rows where every condition is true. It's an implicit AND across all the pairs.

Concrete example. You're tracking sales. Column A holds dates, column B holds salesperson names, column C holds revenue. You want to know how many deals over $5,000 were closed by Sarah this quarter:

=COUNTIFS(A2:A500,">="&DATE(2025,10,1),A2:A500,"<="&DATE(2025,12,31),B2:B500,"Sarah",C2:C500,">5000")

Four range/criteria pairs. Excel walks every row, checks all four conditions, and tallies the rows where everything matches.

All ranges must be the same size. A2:A500 with B2:B100 mixed in will throw #VALUE! because the ranges don't line up. Use the same row span across every pair.

COUNTIFS handles the same wildcards as COUNTIF. Same operator-in-quotes rules. Same concatenation tricks for referencing cells. If you've mastered COUNTIF, COUNTIFS is just the multi-condition version with the same syntax stacked.

Counting distinct values is one of those tasks that sounds basic until you sit down to do it. How many unique customers are in the order list? How many distinct product codes appear in column D? Older Excel doesn't have a dedicated COUNTDISTINCT function, so people have built clever workarounds over the years.

The modern way, if you've got Excel 365 or Excel 2021. Combine UNIQUE with ROWS or COUNTA:

=ROWS(UNIQUE(A2:A100))

UNIQUE returns the list of distinct values as a dynamic array, ROWS counts how many entries are in that array. Clean. Fast. Works.

If you're on older Excel — 2019 and earlier — UNIQUE doesn't exist. The classic SUMPRODUCT trick fills the gap:

=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))

Reads as: for each cell, count how many times its value appears in the range, take the reciprocal (so a value appearing 3 times contributes 1/3 three times = 1), sum all the reciprocals. The result is the count of distinct values. Works in every Excel version going back decades.

Both formulas trip up on blank cells in the range — SUMPRODUCT throws #DIV/0! because COUNTIF returns zero for the blanks. Wrap with IFERROR if blanks are possible, or filter them out first.

For distinct counts with a condition — "how many unique customers spent over $1,000?" — combine with COUNTIFS in an array context, or build a helper column. Or just use a Pivot Table with the Distinct Count option (Excel 2013+), which sidesteps the formula gymnastics entirely.

Counting how many times a specific word appears across a range — not just cells containing the word, but every individual instance — needs a different trick. If a single cell contains "Excel Excel Excel", a COUNTIF for "*Excel*" would only count that one cell. You want a count of 3.

The pattern uses LEN and SUBSTITUTE together:

=SUMPRODUCT((LEN(A2:A100)-LEN(SUBSTITUTE(LOWER(A2:A100),"excel","")))/LEN("excel"))

Breaking it down — LEN gives you the length of each cell. SUBSTITUTE removes every instance of "excel" (lowercased for case-insensitivity). Subtracting the two lengths tells you how many characters disappeared. Divide by the length of the word and you get the count of times the word appeared per cell. SUMPRODUCT sums it all up.

Case-insensitive thanks to the LOWER wrap on both the range and the search term. If you want case-sensitive counting, drop the LOWER. Note that this pattern counts partial-word matches too — "excellent" would contribute one count of "excel" inside it. For whole-word-only matching, you'd need to pad each cell with spaces and search for " excel " with spaces around it, which gets ugly fast. Most of the time the partial-match version is fine.

Counting months between two dates comes up constantly in reporting — tenure calculations, project durations, loan terms, subscription lengths. Excel doesn't have a built-in COUNT-style function for it, but DATEDIF gets the job done.

Syntax — =DATEDIF(start_date, end_date, "M"). The third argument "M" tells Excel to return the count of complete months between the two dates.

Example. Employee start date in A2, today's date as the endpoint. Months of tenure: =DATEDIF(A2,TODAY(),"M").

DATEDIF is one of those functions Microsoft never officially documents but keeps for backward compatibility — Excel won't autocomplete it as you type, but it works in every version. The third argument has options for "Y" (full years), "D" (days), and "MD", "YM", "YD" for various partial-period combos. Stick with M, Y, or D for clean integer counts.

If the start date is later than the end date, DATEDIF returns #NUM!. Always put the earlier date first. If you're not sure which is earlier, wrap with MIN and MAX: =DATEDIF(MIN(A2,B2),MAX(A2,B2),"M").

Counting cells with specific text comes up in audit work a lot. You imported a CSV, half the rows say "TBD", and you want to know exactly how many.

For an exact match — =COUNTIF(A2:A100,"TBD"). Wraps the text in quotes, COUNTIF handles the case-insensitive match.

For cells containing the text anywhere — =COUNTIF(A2:A100,"*TBD*"). Wildcards on both sides match TBD as a standalone word or as part of "TBD-pending" or "MTBD".

For cells where the text starts with "TBD" — =COUNTIF(A2:A100,"TBD*").

For cells where any text exists at all (not just specific words) — =COUNTIF(A2:A100,"*"). The asterisk-only criteria matches any text content. Numbers and blanks don't match.

For text-only cells (excluding numbers and blanks), this is the cleanest one-liner you'll find. Pair with COUNTBLANK and COUNT and you have a full breakdown of any range — total text count, total numeric count, total blanks, total non-blanks.

To count numbers within a specific range, you stack two COUNTIF formulas with subtraction, or just use COUNTIFS with two conditions.

Count numbers between 100 and 500 (inclusive). COUNTIFS handles it cleanly: =COUNTIFS(A2:A100,">=100",A2:A100,"<=500").

Two ranges, two criteria, same range repeated. Excel checks both conditions per row and counts where both are true.

You could also do it with subtraction: =COUNTIF(A2:A100,">=100")-COUNTIF(A2:A100,">500"). Same answer. The COUNTIFS version is easier to read and easier to extend if a third condition shows up.

Count numbers in a column. If "column" means the whole Excel column, use =COUNT(A:A). Excel handles whole-column references efficiently — no need to specify the last row. COUNTA, COUNTBLANK, COUNTIF all accept whole-column references too. Just be aware that COUNTBLANK on a whole column returns a huge number because every empty row down to row 1,048,576 counts. Bound the range to your actual data when blanks matter.

Some quick pro moves before we wrap up.

Use the status bar for one-off checks. Highlight a range and look at the bottom of the Excel window. By default it shows Count, Sum, and Average. Right-click the status bar and you can add Count Numerical, Min, Max, and more. Faster than writing a formula when you just want a quick sanity check.

Combine COUNTIF with conditional formatting. Set up a rule that uses =COUNTIF($A$2:$A$100,A2)>1 as the format condition and Excel highlights every duplicate value in the range. Two clicks and your dupes are visible.

Use COUNTIFS for two-dimensional reports. Build a small summary table where row labels and column labels are categories and each cell uses COUNTIFS to count matches. Cross-tab counts without a Pivot Table. Great for live dashboards because formulas update instantly when source data changes.

Wrap counts in IFERROR for clean dashboards. If your range is empty or the criteria fail, =IFERROR(COUNTIFS(...),0) returns zero instead of an error. Dashboards look professional even with sparse data.

Pivot Tables for ad-hoc counting. If you find yourself writing six COUNTIFS to break down a dataset by multiple dimensions, just build a Pivot Table. Drag the field to Values, change "Sum" to "Count" or "Distinct Count", and you've got the whole breakdown in seconds. Formulas are great for live dashboards. Pivot Tables are great for exploration.

Test wildcards in isolation. If a COUNTIF with wildcards returns an unexpected number, drop the criteria into a single cell first and test with one row. Use COUNTIF for the test too: =COUNTIF(B2,"*Excel*"). Returns 1 if the wildcard matches the single cell, 0 if not. Helps you debug without breaking your head.

The COUNT family is small, but it covers almost every counting question you'll ever face in Excel. Plain COUNT for numbers. COUNTA for non-blanks. COUNTBLANK for the gaps. COUNTIF for one condition. COUNTIFS for many. Master those five and the rest — distinct counts, word counts, month spans, wildcard matching — falls into place using SUMPRODUCT, DATEDIF, LEN, and SUBSTITUTE as supporting cast.

The biggest gotcha is forgetting which function ignores what. COUNT skips text. COUNTA skips truly empty cells. COUNTBLANK counts formulas returning empty strings as blank. COUNTIF and COUNTIFS treat text comparisons case-insensitively unless you reach for SUMPRODUCT with EXACT. Keep a sticky note with those rules until they're second nature.

Practice is what makes the COUNT family click. Build a small spreadsheet with a column of mixed numbers, text, blanks, and formula-returned empty strings. Run each function on it and compare the results — you'll feel the differences in your bones instead of memorizing them from documentation. Ready to test what you've learned? The practice quizzes below cover the full COUNT family, wildcards, and the common gotchas you'll meet on any Excel certification exam.