To count cells in Excel that match a value, use =COUNTIF(A2:A20,"Apple") when the values are in A2:A20 and you want to count cells containing Apple. COUNTIF counts matching cells without regard to letter case; use a cell reference instead of the quoted word if the value may change.
What COUNTIF does and how its syntax works
COUNTIF checks one range of cells against one condition and returns the number of cells that meet it. It is useful for quick counts such as how many times a product appears, how many records have a particular status, or how many scores exceed a threshold. The result is a number, so you can use it in other calculations, charts, or summaries.
Its two arguments are the range to inspect and the criterion to apply. For example, with product names in A2:A20 and a need to count Apple, the formula is =COUNTIF(A2:A20,"Apple"). The range can be a row, column, or rectangular group of cells. COUNTIF does not require the criterion to be in the same range; it can be a typed value or a reference to another cell.
In Microsoft 365 for Windows, select the cell where you want the result, enter the formula, and press Enter. The formula can also be entered through the Formulas ribbon, but typing it directly is usually quickest. If you copy the formula elsewhere, Excel normally adjusts relative references; use dollar signs in a range, such as $A$2:$A$20, if you need that range to stay fixed when copying.
Count an exact text or cell value
For a fixed text value, put the word or phrase in straight double quotation marks inside the formula. With customer names in B2:B50, for example, the following counts cells whose contents match Northwind: =COUNTIF(B2:B50,"Northwind"). The quotation marks belong to the formula and are not part of the value being matched.
If you will change the criterion often, place it in a cell and refer to it. With names in B2:B50 and the target name in D2, use =COUNTIF(B2:B50,D2). If D2 contains Northwind, the result is the count of matching cells. This approach is easier to maintain than editing a quoted word in several formulas, and it can be copied down a summary table when each row has a different criterion.
COUNTIF is not case-sensitive: Northwind, NORTHWIND, and northwind are treated as the same text for this purpose. It does distinguish different text, extra characters, and usually leading or trailing spaces. Thus, a cell containing Northwind with an accidental trailing space may not count as a clean Northwind match. COUNTIF is also not a test for whether a cell merely contains a word; use wildcard characters for that kind of search.
Count numbers and values that meet a threshold
COUNTIF works with numbers as well as text. With scores in C2:C30, this counts scores equal to 80: =COUNTIF(C2:C30,80). A number criterion does not need quotation marks. You can also use comparison operators such as greater than, less than, greater than or equal to, and not equal to. Because the operator is part of a text criterion, it goes inside quotation marks.
With scores in C2:C30, this counts values greater than 80: =COUNTIF(C2:C30,">80"). To use a changing threshold, put the threshold in E2; with scores still in C2:C30, use =COUNTIF(C2:C30,">"&E2). The ampersand joins the comparison operator to the value in E2. If E2 is 80, the formula counts values strictly above 80, not scores equal to 80.
Dates are stored as numeric values in Excel, so comparisons can work for dates too. With order dates in D2:D100 and the start date in F2, this counts dates on or after the date in F2: =COUNTIF(D2:D100,">="&F2). Using a cell reference avoids typing a date in a format that could be interpreted differently by regional settings. Make sure the dates in the data and the criterion cell are real Excel dates rather than text that only looks like a date.
Use wildcards for partial text matches
When you want to count cells that contain a word as part of a longer entry, COUNTIF supports two wildcard characters. An asterisk (*) stands for any number of characters, including none; a question mark (?) stands for exactly one character. With product descriptions in A2:A40, this counts cells containing the text cable anywhere: =COUNTIF(A2:A40,"*cable*"). It can match entries such as USB cable and cable organizer.
Wildcards can also match text at a particular position. With codes in A2:A40, this counts entries that start with AB: =COUNTIF(A2:A40,"AB*"). To count four-character entries beginning with AB, use =COUNTIF(A2:A40,"AB??"). A wildcard search is not the same as an exact match, so avoid adding asterisks when the entire cell must equal the criterion.
If the value you need to match includes an actual asterisk or question mark, precede that character with a tilde in the criterion. With symbols in A2:A40, this counts cells that contain a literal asterisk: =COUNTIF(A2:A40,"~*"). The tilde is COUNTIF’s escape character for wildcards. This distinction matters when counting codes or labels that use those symbols as ordinary characters.
Count blanks, nonblank cells, and multiple conditions
A blank criterion is useful for finding incomplete records. With entries in A2:A100, =COUNTIF(A2:A100," ") counts cells containing a single space, not truly empty cells. To count blank cells, use an empty quoted criterion instead: =COUNTIF(A2:A100,""). To count cells that are not blank, use =COUNTIF(A2:A100,"<>"). Keep in mind that formulas returning an empty string can behave like blanks in counts, so check the underlying cells if the result seems unexpected.
COUNTIF applies only one criterion to one range. If a question involves two or more conditions, use COUNTIFS, which applies multiple criteria and counts rows where all the conditions are met. With department names in A2:A100 and status values in B2:B100, this counts rows where the department is Sales and the status is Open: =COUNTIFS(A2:A100,"Sales",B2:B100,"Open"). The ranges should have matching dimensions so each department and status are evaluated as a corresponding row.
Common reasons a COUNTIF result is wrong
If the count is zero or smaller than expected, first check the range and criterion, then inspect the source values. A formula can be syntactically valid while pointing at the wrong rows. A few specific checks often identify the issue:
- Extra spaces: Look for leading or trailing spaces in imported data. A cell that appears to say Open may contain a space after the word. Cleaning the source data can be more reliable than building increasingly complicated criteria.
- Text versus numbers: A number stored as text may not behave like a numeric value in every comparison. Check whether entries are truly numeric, especially after importing data from another system.
- Wildcards: An asterisk or question mark in the criterion changes an exact search into a pattern search. Escape a literal wildcard with a tilde when needed.
- Range size: Confirm that the range includes all intended records and excludes headers or unrelated totals. If copying a formula changes its range, use absolute references where appropriate.
- Formatting versus content: A cell’s display format can make a value look different without changing the stored value. For instance, a number may display with leading zeros because of formatting, while its underlying value is still numeric.
COUNTIF does not distinguish uppercase from lowercase. If a task truly requires case-sensitive counting, COUNTIF alone is not the right tool; a formula using other functions or a helper column may be needed. Also note that COUNTIF supports a single range and criterion, while COUNTIFS is the clearer choice for multiple conditions. Separating these tasks prevents a common misconception that adding another argument to COUNTIF will create a second test.
COUNTIF compared with COUNTIFS and other options
Choose COUNTIF for one condition, such as counting every entry equal to a status or above a score. Choose COUNTIFS when you need several conditions to be true on the same record, such as a department and a date window. If you want to count cells based on a condition in a different way, such as counting numbers only or counting visible rows after filtering, other functions or approaches may be more suitable. The best choice depends on what qualifies as a match, not just on which formula is shortest.
For a small worksheet, a direct COUNTIF formula is easy to read and update. For a reusable report, storing criteria in cells makes the formula more flexible. Excel tables can also help keep ranges aligned as rows are added, although the exact structured-reference formula depends on the table and column names. For a one-time count, Excel’s status bar may show a count for selected cells, but that is not a replacement for a formula when the result must remain visible, update automatically, or feed another calculation.
Frequently Asked Questions
Does COUNTIF count only an exact match? Without wildcards, COUNTIF checks whether a cell matches the criterion rather than merely containing it. Add an asterisk before and after text when you want a partial match. Remember that * and ? have wildcard meanings unless escaped with a tilde.
How do I count a value stored in another cell? Put the target value in a cell and use that cell as COUNTIF’s second argument. This keeps the criterion editable and avoids manually changing the formula each time the target changes.
Is COUNTIF case-sensitive? No. COUNTIF treats uppercase and lowercase versions of text as equivalent. Use a different approach if capitalization must be part of the match.
Can COUNTIF count cells that are not equal to a value? Yes. Use the not-equal operator (<>) as a criterion, such as <>Open to count cells that do not equal Open. Decide how blanks should be handled, because they can affect what a not-equal test counts.
Why does COUNTIF return zero when the value looks correct? Check for spaces, text-versus-number differences, a misplaced range, and wildcard characters in the criterion. Also verify that dates are actual Excel date values if you are comparing dates.
Can COUNTIF test two conditions at once? Not by itself; it accepts one criterion. Use COUNTIFS for multiple conditions, such as matching a department and a status on the same row.
Related
- Excel Data Model Measures: Add, Format, and Use One
- Excel Data Model: How to Append Tables with Power Query
- Excel Data Run Analysis: A Practical Runs Test
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculate Percentage in Excel: Formulas and Table TipsTo calculate a percentage in Excel, divide the part by the total, then format the result as a…
- How to Lock Cells in Excel Formulas With the F4 ShortcutTo lock a cell reference in an Excel formula, edit the formula, select the reference, and press F4.…
- Excel Formula to Round Up to the Nearest Thousand in ExcelTo round a number in cell A2 up to the next thousand in Excel, use =ROUNDUP(A2,-3). The negative…
- XLOOKUP Formula: Real Examples in ExcelThe basic XLOOKUP formula is =XLOOKUP(E2,A2:A6,B2:B6): it looks for the value in E2 within A2:A6 and returns the…
- What-If Analysis in Excel: A Step-by-Step GuideTo add what-if analysis in Excel, open the Data tab and choose Forecast > What-If Analysis, then select…
- Excel SEQUENCE: Create Automatic Number SequencesYes. In Microsoft Excel for Microsoft 365, enter a SEQUENCE formula to generate a series automatically: with an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.