Excel’s COUNTIF function counts cells in a range that meet one condition. Give it a range to inspect and a criterion to test—for example, with product names in A2:A20, =COUNTIF(A2:A20,"Tea") returns the number of cells containing Tea.
What COUNTIF does
COUNTIF is a worksheet function for answering a focused question about a group of cells: how many meet a specified condition? It returns a number, so it can support a quick check, a summary table, a dashboard, or a larger calculation. It does not change or mark the cells it examines.
The function has two arguments: range, the cells to check, and criteria, the condition a cell must satisfy to be counted. The condition can be a number, text, comparison, wildcard pattern, or reference to a cell containing the condition. COUNTIF handles one range and one condition at a time. If the question involves multiple conditions, use COUNTIFS instead.
COUNTIF is available in Microsoft Excel for Microsoft 365 on Windows, as well as other current Excel editions. Function names and argument separators can vary with language and regional settings; the examples here use English function names and commas.
How COUNTIF evaluates a range
Excel checks the cells in the supplied range against the criterion, then returns the count of cells that match. For example, if a range contains four occurrences of the word Open, a criterion for Open returns 4. Cells that do not match are ignored; COUNTIF does not count the number of different values or add the values in the range.
Text comparisons are not case-sensitive: Open, OPEN, and open match the same text criterion. A plain text criterion generally matches the complete cell contents, not just a word appearing somewhere inside a longer entry. Wildcards let you ask for a partial text match instead.
The criterion argument can be written directly in the formula or supplied by a cell reference. A direct criterion is convenient for a one-off calculation. A reference is useful when people need to change the condition without editing the formula. For comparison operators stored separately from their value, join the operator and cell reference with the ampersand (&).
Counting exact text and numbers
For a text match, put the word inside straight double quotation marks. With department names in B2:B50, =COUNTIF(B2:B50,"Sales") counts cells whose contents match Sales. It does not distinguish uppercase from lowercase. If the entries contain extra spaces, inconsistent spelling, or added words, those differences can prevent a plain exact-text match.
Numbers can be used as criteria without quotation marks. With scores in C2:C30, =COUNTIF(C2:C30,100) counts cells whose numeric value is 100. Excel also accepts a quoted numeric criterion in many common cases, but using an actual number is clear when the condition is an exact numeric value. A number stored as text may behave differently from a real number, so check the underlying cell values if a count seems unexpectedly low.
Comparison operators allow ranges such as “greater than” or “less than or equal to.” With scores in C2:C30, =COUNTIF(C2:C30,">=80") counts scores of at least 80. When the limit is in another cell, say E1, use =COUNTIF(C2:C30,">="&E1). The operator is in quotation marks, and the ampersand joins it to E1’s value to form the criterion.
Using cell references and reusable criteria
A criterion cell makes a formula easier to maintain. With order statuses in D2:D100 and a chosen status in F2, =COUNTIF(D2:D100,F2) counts orders matching the current value in F2. Change F2 from Pending to Shipped, and the result recalculates without rewriting the formula.
For comparisons based on a value in a cell, the operator must be joined to the reference. With quantities in E2:E100 and a threshold in F2, =COUNTIF(E2:E100,">"&F2) counts quantities above that threshold. Putting the operator in the reference cell as part of the criterion text is another option, but it can make the setup less clear to other workbook users.
When copying a formula down a column, decide whether the range should move. With data in A2:A100, =COUNTIF($A$2:$A$100,C2) keeps the data range fixed while allowing the criterion reference to change from C2 to C3 as the formula is copied. Dollar signs make a reference absolute; pressing F4 while editing or selecting a reference cycles through reference styles in Excel for Windows. On a Mac, the equivalent shortcut is usually Command+T, though keyboard settings can affect it.
Excel Tables can make expanding data easier to manage. If a table named Orders has a Status column, =COUNTIF(Orders[Status],"Open") counts Open entries in that column. A structured reference typically adjusts as rows are added to the table, unlike a manually chosen range that may need extending.
Wildcards for partial text matches
COUNTIF supports two wildcards in text criteria. An asterisk (*) matches any sequence of characters, including no characters; a question mark (?) matches one character. These are useful when entries share a pattern but not an identical full value.
- With item names in A2:A40,
=COUNTIF(A2:A40,"Pen*")counts text entries that begin with Pen, such as Pen and Pencil. - With email addresses in B2:B100,
=COUNTIF(B2:B100,"*@example.com")counts entries ending in @example.com. - With codes in C2:C30,
=COUNTIF(C2:C30,"A?7")counts entries matching A, then any one character, then 7.
If the text you want to find includes a literal asterisk or question mark, put a tilde (~) before that character in the criterion. For instance, with notes in A2:A50, =COUNTIF(A2:A50,"~*") counts cells containing a literal asterisk as the matched text. The tilde is the escape character; without it, the asterisk means “any sequence.”
Counting blanks, dates, and common categories
Blank criteria are helpful for checking incomplete records. With contact names in A2:A100, =COUNTIF(A2:A100,"") counts cells treated as blank. This can include cells with formulas that return an empty string, depending on the cell contents and Excel’s criteria evaluation. To count cells that contain something rather than are blank, with the same layout use =COUNTIF(A2:A100,"<>"). If the result matters for data cleanup, test it against a few known blank cells and formulas.
Dates in Excel are stored as serial numbers, so date criteria should normally be built from real date values rather than typed in an ambiguous text format. With dates in D2:D200 and a real Excel date in F1, =COUNTIF(D2:D200,">="&F1) counts dates on or after the date in F1. To count a particular day, ensure the cells contain dates without different time values; a date-time value later on the same day is not identical to midnight on that date.
A category count is often the starting point for a simple report. With the word Yes or No in E2:E500, =COUNTIF(E2:E500,"Yes") counts affirmative responses. For summary tables, put each category label in a cell and point the criterion argument to that cell, so the same formula pattern can be filled down beside a list of categories.
COUNTIF versus COUNTIFS and other options
COUNTIF is the right choice when every counted cell is judged by one condition. For example, it can count all orders with the status Open. It cannot, by itself, apply a separate condition to a second range, such as counting Open orders for one particular region.
COUNTIFS handles multiple conditions and applies them together: a row must satisfy every supplied range-and-criterion pair. With statuses in D2:D100 and regions in E2:E100, =COUNTIFS(D2:D100,"Open",E2:E100,"West") returns the number of rows that are both Open and West. The ranges in a COUNTIFS formula should be the same size and shape so corresponding cells are evaluated as records.
Other functions can be more appropriate for related tasks. COUNT counts numeric cells, COUNTA counts cells that are not empty, and COUNTBLANK counts blank cells. COUNTIF is more flexible than these when you need a condition, but it is not a substitute for adding matching values—that task calls for SUMIF or SUMIFS. For a simple one-condition count, COUNTIF is generally the most direct option.
Common problems and how to fix them
- The answer is zero: Check for leading or trailing spaces, misspellings, inconsistent data types, or a criterion that does not match the entire cell. For a partial text match, use wildcards.
- A comparison does not work: When the comparison limit is in a cell, join the operator to the reference with an ampersand. A criterion written as a quoted operator followed by a cell reference without joining them will not behave as intended.
- The count changes when copied: Review whether the range needs dollar signs to stay fixed. Keep a relative criterion reference if it should update for each row.
- A date count looks wrong: Confirm that the cells contain genuine Excel dates and not text that only looks like a date. Also consider whether some cells include times.
- The formula shows an error: Check that parentheses and quotation marks are balanced and that the range and criterion are separated correctly. Some regional Excel settings use semicolons rather than commas between arguments.
COUNTIF is not case-sensitive. If uppercase and lowercase must be treated as different values, COUNTIF alone is not enough; a case-sensitive approach requires a different formula design. Likewise, it is worth checking the source data rather than repeatedly changing the criterion when a count suggests that a column contains inconsistent entries.
Frequently Asked Questions
Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF’s text matching is case-insensitive, so a criterion for North also matches NORTH and north. If capitalization itself carries meaning in the data, use a case-sensitive method instead of relying on COUNTIF.
Can COUNTIF check two conditions?
Not in one COUNTIF call. Use COUNTIFS to combine conditions, such as a status condition and a region condition, with a matching range for each one. Every condition in COUNTIFS must be true for a row to contribute to the count.
How do I count cells containing part of a word?
Use an asterisk before and/or after the text to match any sequence of characters. For example, a criterion with a word surrounded by asterisks can match that word within a longer text entry. Use a question mark when exactly one character may vary.
Why does COUNTIF return zero when the value looks correct?
Common causes include hidden spaces, a number stored as text, a date stored as text, or a criterion intended as a partial match but written as an exact match. Compare the underlying values and try a wildcard when the target text may be part of a longer entry.
Can COUNTIF count dates in a range?
Yes. Excel stores real dates as numbers, so COUNTIF can compare them using criteria such as on or after a date. Use a cell containing a real date as the limit where possible, and account for time values if the date cells also contain times.
Choosing a reliable COUNTIF formula
Start by stating the question precisely: which cells should be checked, and what makes one of them a match? Then choose an exact value, comparison operator, cell reference, wildcard pattern, or blank criterion to express that condition. Test the result against a small set of entries you can count manually, especially when the range includes dates, formulas, or imported text. Once the criteria match the data as intended, COUNTIF provides a compact and reusable way to turn a range of cells into a clear count.
Related
- Excel Work Schedule Template: Build and Customize One
- Excel Work Timesheet Template: Build One That Adds Work Hours
- Excel Won’t Group Dates in a PivotTable? Fixes That Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LET Function: Make Advanced Formulas ClearerYes—Excel lets you build advanced formulas, and the LET function makes them easier to read by giving names…
- Excel INDEX Absolute References: When and How to Use ThemYes, an Excel INDEX formula can use absolute references, and locking its ranges is often helpful when you…
- CONCAT in Excel: Add a Space Between Text ValuesTo add a space when joining text in Excel, include a space in quotation marks as an argument:…
- Number Sequence in Excel: Fill Series or Use a FormulaTo create a number sequence in Excel, enter the first two numbers, select both cells, and drag the…
- Excel EDATE Function: Add or Subtract Months from a DateUse Excel’s EDATE function to move a date forward or backward by a specified number of whole months:…
- Excel COUNTIF Criteria: Rules, Examples, and FixesIn Excel, COUNTIF criteria are the conditions that tell the COUNTIF function which cells to count. The syntax…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.