The COUNTIF function in Excel is one of the most useful tools you'll reach for on a daily basis. It counts cells in a range that meet a single condition — no complex setup, no helper columns. Just one formula that answers questions like "how many times does 'Approved' appear in this column?" or "how many sales exceeded $500?"
Before you dig into advanced formulas, it helps to have a solid grasp of the Excel definition and core concepts. But even beginners can start using COUNTIF immediately. The syntax is straightforward, and the logic is intuitive once you see a few examples.
This guide covers everything — basic syntax, wildcard matching, comparison operators, date counting, the multi-criteria sibling COUNTIFS, and the mistakes that trip people up most often.
The full syntax is: =COUNTIF(range, criteria)
Two arguments. That's it.
A2:A100, a row, or a named range.Excel scans each cell in the range, checks whether it matches the criteria, and returns a count of matching cells. It's case-insensitive by default — "apple" and "Apple" both count the same way.
One limitation worth knowing upfront: COUNTIF only handles a single condition. If you need two or more conditions applied simultaneously, you'll use COUNTIFS (covered below). But for most everyday counting tasks, a single COUNTIF gets the job done fast.
The cells you want to evaluate
The condition each cell must meet to be counted
Let's look at the most common use cases you'll run into.
Say column B contains order statuses. You want to count how many orders are "Shipped":
=COUNTIF(B2:B100, "Shipped")
The criteria is wrapped in quotes because it's a text string. Excel counts every cell in B2:B100 that contains exactly "Shipped".
Instead of hardcoding the value, point to a cell:
=COUNTIF(B2:B100, E2)
If E2 contains "Shipped", the formula counts the same way. This approach is more flexible — change E2 and the count updates automatically. No formula editing required.
Numbers don't need quotes:
=COUNTIF(C2:C50, 100)
This counts every cell in C2:C50 that equals exactly 100. You can also quote numbers — "100" — and COUNTIF still works, but it's cleaner without quotes for numeric criteria.
To count all cells that aren't blank:
=COUNTIF(A2:A200, "<>")
The <> operator means "not equal to", and leaving the value blank means "not empty". Handy for data completeness checks.
For blanks specifically:
=COUNTIF(A2:A200, "")
Two quotes with nothing between them = empty string = blank cell. Though for this specific task, COUNTBLANK is also an option.
Wildcards let you match partial text — incredibly useful when your data isn't perfectly consistent.
Excel supports two wildcards in COUNTIF criteria:
Count all cells containing the word "apple" anywhere:
=COUNTIF(A2:A100, "*apple*")
The asterisks on both sides mean "anything before or after apple". So "apple juice", "green apple", and "pineapple" all match.
Count cells starting with "Pro": =COUNTIF(A2:A100, "Pro*")
Count cells ending with "2024": =COUNTIF(A2:A100, "*2024")
The question mark matches exactly one character:
=COUNTIF(A2:A100, "b?t")
This matches "bat", "bit", "bot", "but" — any three-letter word starting with "b" and ending with "t". Not something you'll use every day, but essential for matching codes with a fixed-length format.
Concatenate a wildcard with a cell reference using the ampersand:
=COUNTIF(A2:A100, "*"&E2&"*")
If E2 contains "report", this counts any cell with "report" anywhere in the text. Great for dynamic search-style formulas where users type a keyword and the count updates live.
If your data contains asterisks or question marks you want to match literally, prefix with a tilde (~):
=COUNTIF(A2:A100, "~*")
The tilde tells Excel to treat what follows as a regular character, not a wildcard.
When you need to count cells based on a value threshold — greater than, less than, equal to — you use comparison operators inside the criteria string.
All comparison operators in COUNTIF must be wrapped in quotes as part of the criteria string:
> — greater than< — less than>= — greater than or equal to<= — less than or equal to<> — not equal toCount sales greater than $1,000: =COUNTIF(C2:C200, ">1000")
Count scores of 70 or higher: =COUNTIF(D2:D50, ">=70")
Count items that aren't "Cancelled": =COUNTIF(B2:B100, "<>Cancelled")
Count values below the average — note the concatenation trick:
=COUNTIF(C2:C200, "<"&AVERAGE(C2:C200))
That last one is powerful. You can combine the < operator string with any calculated value using &. The formula builds criteria dynamically at runtime — no hardcoding required.
This same pattern works with cell references: =COUNTIF(C2:C200, ">"&F1)
Where F1 contains your threshold. Change F1, the count updates automatically. If you're building dashboards with dynamic filtering, this operator-plus-reference pattern is something you'll use constantly. Combined with conditional formatting in Excel, you can highlight rows while COUNTIF tracks the totals automatically.
Dates in Excel are stored as serial numbers under the hood, so COUNTIF can count them using the same comparison operators. The key is formatting your criteria correctly so Excel interprets them as date values, not text strings.
The safest approach uses the DATE function:
=COUNTIF(A2:A100, DATE(2024, 12, 31))
Avoid typing dates as text strings like "12/31/2024" — regional date format differences can break the formula silently on someone else's machine.
=COUNTIF(A2:A100, ">"&DATE(2024, 6, 1))
This counts all dates after June 1, 2024. The concatenation trick from the previous section applies here too.
=COUNTIF(A2:A100, "<"&TODAY())
TODAY() returns the current date as a serial number. This formula counts all past dates and updates automatically each day — no maintenance needed. It's ideal for overdue-item tracking.
COUNTIF alone can't filter by month cleanly. Use two COUNTIF formulas and subtract:
=COUNTIF(A2:A100, ">="&DATE(2024,3,1)) - COUNTIF(A2:A100, ">="&DATE(2024,4,1))
This counts dates in March 2024 — all dates on or after March 1, minus all dates on or after April 1. For more complex date filtering, COUNTIFS handles it more cleanly (see the next section).
COUNTIFS is the multi-condition version of COUNTIF. It counts cells only when ALL specified criteria are met simultaneously.
Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, ...)
You can add as many range/criteria pairs as you need — each range must be the same size as the others.
Count orders from the "West" region with a value over $500:
=COUNTIFS(B2:B100, "West", C2:C100, ">500")
Both conditions must be true for a row to count. Region = West AND amount > 500. If either condition fails, the row doesn't count.
COUNTIFS makes date-range counting much cleaner:
=COUNTIFS(A2:A100, ">="&DATE(2024,3,1), A2:A100, "<"&DATE(2024,4,1))
Same range, two criteria — both applied. Much more readable than the subtract approach.
Add more range/criteria pairs as needed:
=COUNTIFS(B2:B100, "West", C2:C100, ">500", D2:D100, "Shipped")
Region = West AND amount > 500 AND status = Shipped. All three must be true. There's no practical upper limit on the number of criteria pairs you can chain together.
Reference the same range multiple times to create a between condition:
=COUNTIFS(C2:C100, ">=100", C2:C100, "<=500")
Counts values between 100 and 500 inclusive — the standard pattern for between-range queries. Much cleaner than the subtract approach.
Start with a plain English question: 'How many orders are Shipped?' or 'How many scores are above 80?' Your question tells you what goes in the criteria argument.
Decide which column or row contains the data to check. Use A2:A100 for a specific block, or A:A for the whole column if your data size varies. Exclude header rows when possible.
Text values go in quotes: "Shipped". Numbers go without: 100. Comparison operators go inside quotes with the value: ">80". Wildcards use asterisks: "*partial*". Cell references go without quotes: A1.
When your threshold is in a cell (like F1) or from a function (like AVERAGE), concatenate the operator with &: ">=" & F1 or ">" & AVERAGE(C:C). This makes your formula fully dynamic.
Cross-check your COUNTIF result by filtering the column and checking the row count in the status bar. They should match. If they don't, check for data type mismatches or hidden characters.
If you need a second condition, switch to COUNTIFS. The syntax is the same — just add more range/criteria pairs. Your original range and criteria stay exactly as written.
Single condition counting
=COUNTIF(range, criteria)Example: =COUNTIF(B2:B100, "Approved") — counts all "Approved" entries in the range.
Multiple condition counting
=COUNTIFS(r1, c1, r2, c2, ...)Example: =COUNTIFS(B2:B100, "West", C2:C100, ">500") — West region orders over $500.
Conditional summing — not counting
=SUMIF(range, criteria, sum_range)Example: =SUMIF(B2:B100, "West", C2:C100) — total sales value for West region only.
These three functions look similar but answer different questions. Picking the wrong one means extra formula gymnastics for no reason.
Use COUNTIF when you need a count based on one condition. It's the fastest option for frequency analysis — how many times a value appears, how many cells exceed a threshold, how many entries match a status.
Use COUNTIFS when you need a count with two or more conditions that must all be true simultaneously. Any time you catch yourself writing COUNTIF() * COUNTIF(), stop — COUNTIFS is the right tool.
Use SUMIF when you want to add up values based on a condition — not count rows, but produce totals. Total revenue for a specific region, hours logged for a specific employee. SUMIFS handles multiple conditions for summing.
Quick decision rule: if your question starts with "how many" — COUNTIF or COUNTIFS. If it starts with "how much" or involves dollar amounts — SUMIF or SUMIFS.
These functions shine together in data cleanup. After you delete duplicates in Excel, running a COUNTIF on the cleaned data confirms whether any duplication remains — a fast sanity check that takes seconds.
Even a simple function has its gotchas. Here are the errors you'll hit most often and how to solve them quickly.
This appears when the criteria string exceeds 255 characters, or there's a data type mismatch in an edge case. Fix: Keep criteria strings short. If you need complex matching, break it into multiple COUNTIF formulas and add the results.
The most common cause: invisible characters in your data. Extra spaces, line breaks, or non-printing characters prevent a match even when the cell looks correct on screen.
Fix: Use TRIM() on your data range. If that doesn't work, check whether cells contain text vs numbers — a number stored as text won't match a numeric criteria. Try VALUE() to convert, or use Text-to-Columns to reformat the column.
COUNTIF doesn't natively support 3D references. This formula doesn't work: =COUNTIF(Sheet1:Sheet3!A:A, "Value")
Fix: Write a COUNTIF for each sheet and sum them:
=COUNTIF(Sheet1!A:A, "Value") + COUNTIF(Sheet2!A:A, "Value") + COUNTIF(Sheet3!A:A, "Value")
Or consolidate your data into one sheet first — usually better practice anyway.
Forgetting the quotes around operators is the most common syntax mistake. Wrong: =COUNTIF(C:C, >50). Right: =COUNTIF(C:C, ">50"). The operator and value must be inside quotes.
Your range includes the header row and it matches your criteria. Fix: start your range at row 2 (A2:A100 instead of A1:A100).
Once you're comfortable with the basics, these patterns unlock more powerful analysis without complex formulas.
COUNTIF doesn't directly count uniques, but you can use it inside SUMPRODUCT:
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))
This works by counting how many times each value appears, then summing the reciprocals — counting each unique value exactly once. Note: this breaks with blank cells, so filter those out first.
In conditional formatting rules, COUNTIF powers duplicate highlighting. A custom rule like =COUNTIF($A$2:$A$100, A2)>1 applied to a range highlights every value that appears more than once.
This is far more flexible than the built-in "Highlight Duplicates" preset. You can pair it with conditional formatting in Excel to create data-quality dashboards that flag issues automatically.
What percentage of your data meets a condition? Divide COUNTIF by COUNTA:
=COUNTIF(B2:B100, "Approved") / COUNTA(B2:B100)
Format the result as a percentage and you've got instant approval rates, completion rates, or pass rates — no separate column needed.
After importing data, run COUNTIF to verify expected counts. If you're expecting 50 entries per region but COUNTIF for "North" returns 47, something got dropped. It's a quick audit step that catches problems before they spread through your analysis.
Before building pivot tables, use COUNTIF to verify dropdown list values match actual data. If you've set up how to add drop down list in excel for data entry, a COUNTIF check confirms entries match valid options and none were typed freehand.
If your data grows over time, avoid hardcoding the range end row. Use whole-column references like A:A instead of A2:A100. Excel stops scanning at the last cell with data — it doesn't crawl a million empty rows.
To build stronger Excel skills, practice these patterns on real datasets. Replace your manual filter-and-count workflow with COUNTIF formulas — when the results match, you know you've got it right.
COUNTIF is one of those functions that seems basic until you realize how often you're reaching for it — in audit reports, dashboards, data validation scripts, conditional formatting rules, and quick ad-hoc analysis. It's the difference between manually scrolling a spreadsheet and having your answer in two seconds.
The core pattern is always the same: range, then criteria. Everything else — operators, wildcards, date functions, cell references — is just different ways to express that criteria. Once that clicks, you can build any COUNTIF formula without looking up the syntax.
COUNTIFS extends this naturally when conditions pile up. And when your goal shifts from counting to summing, SUMIF and SUMIFS follow the exact same logic — just a different verb.
If you want to sharpen your formula skills beyond COUNTIF, the Excel certification practice test covers the full range of Excel functions you'd encounter in professional environments — COUNTIF, VLOOKUP, INDEX/MATCH, PivotTables, and more. Working through practice questions is the fastest way to build fluency where formulas become second nature.