The COUNTIF function in Excel counts cells that match a single condition you give it. Type a range, type a rule, hit enter, and you get a number. That is it. No setup, no macros, no add-ins. It is one of the first functions most analysts learn, and it stays useful for years because every spreadsheet has data you need to filter and count quickly.
Here is the basic shape: =COUNTIF(range, criteria). The range is the cells you want to look at. The criteria is the rule those cells need to match. For example, =COUNTIF(A2:A100, "Active") counts how many cells in that column contain the text "Active". Swap the word for any value, number, or comparison and Excel handles it.
People often confuse COUNTIF with COUNT or COUNTA. COUNT only handles numbers. COUNTA counts any non-empty cell. COUNTIF lets you filter, which is the part that matters for most reports. Need a count tied to multiple rules? That is where COUNTIFS (with the S) takes over, and we will cover that distinction later.
This guide walks through real examples, common errors, and the workflow questions that come up on the job and on certification exams. Each section is short, hands-on, and tied to the kind of practice you would expect on a Microsoft Excel test. You will see how to count text, numbers, dates, partial matches, blanks, and values from another sheet.
COUNTIF scans a range one cell at a time. For each cell, it checks whether the value satisfies your criteria. If it does, the counter goes up by one. When the scan ends, Excel returns the total. The function does not modify your data, sort anything, or move cells. It is purely a read operation, which is why it is safe to drop into any worksheet.
The criteria can be a literal value, a cell reference, or a comparison expression wrapped in quotes. Numbers do not need quotes when used alone, but anything with an operator does. So =COUNTIF(B2:B50, 100) works, and so does =COUNTIF(B2:B50, ">100"). Mix them up and Excel returns a #VALUE error or, worse, a silent zero.
Wildcards open up partial-match counting. The asterisk (*) stands in for any string of characters. The question mark (?) stands in for any single character. So =COUNTIF(C2:C200, "*report*") counts every cell containing the word "report" anywhere in the text. That trick alone saves hours on messy data.
Picture a help-desk log with 500 rows. Column D holds ticket status: Open, In Progress, Resolved, Closed. Your manager wants a quick tally. You write =COUNTIF(D2:D501, "Open") in one cell, repeat for each status with the label changed, and you have a four-line summary in under a minute. No filters, no PivotTable setup, no sort step.
=COUNTIF(range, criteria) — the range is the cells to scan, the criteria is the rule each cell must match. Quotes go around text values, any comparison expression, and dates. Numbers used alone do not need quotes. Cell references work in place of literal values, which lets you change the criteria from a dropdown without rewriting the formula.
COUNTIF reads numbers and dates the same way it reads text, but operators unlock the real power. Suppose column E has sales totals. You want to know how many sales exceeded 1,000 dollars. Type =COUNTIF(E2:E1000, ">1000") and Excel returns the count. Drop the quotes around the operator and the formula breaks, so keep them.
Dates work the same way once you wrap them in quotes. =COUNTIF(F2:F1000, ">=1/1/2026") counts entries from the start of 2026 onward. To pull dates from a cell, use the ampersand: =COUNTIF(F2:F1000, ">="&G1) where G1 holds your reference date. Excel reads the cell value, glues it to the operator, and counts the result.
If you need a window between two dates, COUNTIF alone will not do it because the function only takes one criteria. You can subtract two COUNTIF calls (one for the upper bound, one for the lower) or move to COUNTIFS. Most analysts switch to COUNTIFS at this point because the syntax is cleaner.
Blanks trip up everyone. To count truly empty cells, use =COUNTIF(A2:A100, ""). To count cells with any value, use =COUNTIF(A2:A100, "<>") or COUNTA, which is shorter. There is a gotcha: a cell holding an empty string from a formula counts as non-blank to COUNTIF but blank to ISBLANK. If your data comes from a query or import, test both and pick the one that matches what you see.
=COUNTIF(A2:A100, "Active") counts every cell whose value equals the word Active. Case is ignored, so Active and ACTIVE both count. This is the most common pattern in status reports and category tallies.
=COUNTIF(B2:B100, ">500") counts numbers above 500. The operator and the value stay inside the quotes together as a single string. Forget the quotes and Excel throws a #NAME or #VALUE error before it even tries to count.
=COUNTIF(C2:C100, "*report*") counts cells containing the word report anywhere in the text. Useful when a column has descriptive strings rather than tidy category labels. Works on text-formatted cells only.
=COUNTIF(D2:D100, E1) reads the criteria from E1. Change the value in E1 and the count refreshes instantly. This pattern powers dropdowns, slicers, and small KPI dashboards.
Wildcards turn COUNTIF into a quick text search. The asterisk wildcard matches zero or more characters. The question mark matches exactly one. Combine them and you can count anything that fits a pattern, not just an exact string.
Say you have a column of product codes like ABC-123, ABC-124, XYZ-001. To count every code starting with ABC, write =COUNTIF(A2:A500, "ABC*"). To count three-letter codes ending in 7, use =COUNTIF(A2:A500, "??7"). For codes containing exactly five characters, you would write five question marks in a row. It looks odd, but it works.
Wildcards do not work on numeric data unless the numbers are stored as text. If your column is formatted as Number, COUNTIF treats them as values and the wildcards are ignored. To force a text comparison, either format the column as Text or wrap your data in a TEXT function inside an array formula. Most people just convert the column.
COUNTIF is not case sensitive. "Open", "OPEN", and "open" all match the same criteria. If you need a case-sensitive count, you have to combine SUMPRODUCT with EXACT, like =SUMPRODUCT(--EXACT(A2:A100, "Open")). That formula respects letter case. For most reporting tasks the default behavior is fine, but exam questions sometimes test whether you know the limitation.
Wrap text in quotes like "Open" or "Resolved". The match ignores case so Open, OPEN, and open all return the same total. Wildcards (asterisk for any string, question mark for a single character) work inside text criteria and dramatically widen what you can match. Watch for trailing spaces — they break exact-match counts every time.
Use the value alone for an equality check or wrap an operator with the value in quotes when you need a comparison: 100 matches exactly one hundred, while ">100" counts everything above one hundred. The same trick works for less-than, greater-than-or-equal, and not-equal-to comparisons, which makes COUNTIF a quick replacement for a filter on a numeric column.
Quote the date and operator together as one string: "<=12/31/2026" counts rows on or before that date. For dynamic references, concatenate the operator with a cell using the ampersand: ">="&G1. The ampersand glues Excel's serial date number to the operator and the comparison resolves correctly.
Use "" for cells that are truly empty and "<>" for cells holding any value. Be careful: cells that look blank but actually contain an empty string from a formula are counted as non-blank by COUNTIF even though ISBLANK reports them as empty. Test both functions on the same range when the numbers feel off and pick the one that matches reality.
Excel ships with both COUNTIF and the multi-criteria COUNTIFS sibling. COUNTIF handles a single rule. COUNTIFS handles two or more rules joined by AND logic. The syntax for COUNTIFS pairs each range with its own criteria, like =COUNTIFS(A2:A100, "Open", B2:B100, ">100"). Each range must be the same size, or Excel throws a #VALUE error.
Use COUNTIF when you only need one condition. Switch to COUNTIFS the moment you need to filter by two columns at once. Some teams default to COUNTIFS for everything because it future-proofs the formula, since you can add criteria later without rewriting the whole thing. The performance cost is negligible on normal-sized data.
COUNTIF works across sheets. Reference the source sheet name, an exclamation mark, then the range: =COUNTIF(Sales!B2:B500, ">1000"). If the sheet name has spaces, wrap it in single quotes: =COUNTIF('Q4 Sales'!B2:B500, ">1000"). The function does not need the source sheet to be active.
Cross-workbook references work too, but they break the moment the other file is closed. For shared reporting, copy the data into the same workbook or use Power Query to import it. Linked workbooks cause more pain than they solve, especially when files move or get renamed.
Three errors come up over and over. The first is #VALUE, which usually means your range and criteria do not match in type. Maybe the column is formatted as Date but the criteria is a string. Convert one or the other and the formula clears.
The second is a result of zero when you expected something. Check for stray spaces in your data. A cell that looks like "Open" but actually contains "Open " (with a trailing space) will not match the criteria "Open". Use the TRIM function in a helper column or run Find and Replace to clean it.
The third is an inflated count from wildcards you did not intend. If your criteria contains a literal asterisk or question mark, escape it with a tilde: =COUNTIF(A2:A100, "~*"). Without the tilde, Excel treats the asterisk as "match anything" and returns the count of every cell.
A fourth gotcha worth mentioning is mixed data types in the same column. If a few cells in a number column were entered as text (a green triangle in the corner usually signals this), COUNTIF on a numeric comparison will skip them. Convert the column to numbers via Paste Special or the VALUE function and the count corrects itself.
COUNTIF is fast on tens of thousands of rows but slows down on hundreds of thousands when stacked dozens of times in a workbook. If your file feels sluggish, switch repeated COUNTIF calls to a PivotTable or use Power Query for the heavy lifting. A single COUNTIF on 100,000 rows recalculates in milliseconds. Two hundred of them on the same range will lag noticeably.
For very large workbooks, consider switching calculation to manual mode while you edit, then hit F9 to recalculate when you are ready. This stops every keystroke from triggering a full recalculation cycle, which is the actual reason big COUNTIF-heavy files feel slow rather than the function itself being inefficient.
Certification exams and job interviews lean on a handful of COUNTIF patterns. Counting orders above a threshold. Counting names matching a department. Counting dates inside a quarter. Counting blanks in a survey column. Counting unique values (technically with a helper formula since COUNTIF alone does not handle uniqueness directly). Working through each one in a real workbook beats memorizing syntax.
Try this drill: open a blank worksheet, paste a list of fifty random names with a status column, and write five COUNTIF formulas in five different cells. Count the active ones. Count the names starting with M. Count the blanks. Count the names with exactly four letters. Count the rows where the status is anything other than Closed. If all five return what you expect, you understand the function well enough for most jobs.
A second drill that helps is the dashboard build. Create a small KPI grid where each cell uses a different COUNTIF to summarise a column. Add a dropdown that swaps the criteria via a cell reference. When you change the dropdown, every count refreshes instantly. This is exactly how production dashboards behave, and exam scenarios often test whether you can wire one up without help.
Two situations call for a different function. If you need to sum values that match a rule, not count them, use SUMIF or its multi-criteria cousin. If you need to look up a value tied to a match, use VLOOKUP or its modern replacement. COUNTIF answers "how many," not "how much" or "which one."
For complex conditions involving OR logic, COUNTIF needs to be repeated and summed: =COUNTIF(A2:A100, "Open") + COUNTIF(A2:A100, "Pending"). Some analysts wrap multiple criteria in an array constant: =SUM(COUNTIF(A2:A100, {"Open","Pending"})). Both work. Pick the style your team reads more easily.
If you need a count that responds to a slicer or a filter on a Table, the SUBTOTAL function is the better choice. SUBTOTAL respects hidden rows from filters, while COUNTIF ignores filter state entirely. Mixing them in the same sheet without realising the difference is a classic source of mismatched numbers in reports.
Lock your ranges with dollar signs the moment you plan to copy a COUNTIF formula across cells. =COUNTIF($A$2:$A$100, B2) stays anchored to the source range while the criteria shifts down with each new row. Skip the dollar signs and you end up counting smaller and smaller windows.
Name your ranges when a workbook gets complex. Instead of =COUNTIF(A2:A500, "Open"), define a name like "TicketStatus" and write =COUNTIF(TicketStatus, "Open"). The formula reads almost like English and survives column rearrangements.
Use COUNTIF inside conditional formatting to flag duplicates. The rule =COUNTIF($A$2:$A$1000, A2)>1 highlights every cell whose value appears more than once. It is the cleanest way to spot duplicates without sorting the column first. Drop the rule in, watch the highlights appear, and decide what to do from there.
COUNTIF gets even more powerful when you nest it inside other formulas. Want a percentage of cells matching a rule? Wrap a COUNTIF in a division: =COUNTIF(A2:A100, "Open") / COUNTA(A2:A100). Format the result as a percentage and you have a ratio that updates the moment data changes.
You can also chain COUNTIF with IF to control branching logic. For example, =IF(COUNTIF(A2:A100, "Open")>5, "Alert", "OK") returns a status label based on the count. This pattern shows up constantly in dashboards where a manager wants a quick green or red indicator.
Even array formulas use COUNTIF as a building block. The classic unique-value count formula, =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)), leans on COUNTIF to figure out how many times each value appears and then sums the reciprocals. The math is clever, and it works once you stop fighting the syntax. In modern Excel, the UNIQUE function does the same job in one step, but the older formula still ships in countless legacy workbooks.
Most analysts use a small handful of functions every day. SUM, AVERAGE, IF, VLOOKUP or XLOOKUP, COUNTIF, and a PivotTable cover at least eighty percent of real workbooks. COUNTIF sits in the middle because counting is the foundation of every report. Before you can chart anything, you usually need to know how many of something exist. Before you can write a summary, you need the totals. COUNTIF is the function that gets you there fastest.
If you are studying for a Microsoft Office Specialist exam or a job interview, drill COUNTIF until the syntax feels automatic. Practice writing it with text, with numbers, with comparisons, with wildcards, and with cell references. Practice fixing the common errors. Practice spotting when COUNTIFS is the better choice. The function is small, but the situations where it shows up are everywhere, and a confident hand with it signals that you actually use Excel and not just that you have read a tutorial about it.