In Excel, the range in COUNTIF is the group of cells the function checks; the basic form is =COUNTIF(A2:A20,"Open"). Select the cells you want evaluated as the first argument, then provide a criterion that tells Excel what to count.
What “range” means in COUNTIF
COUNTIF counts cells in one range that meet one condition. A range is a reference to one or more cells, such as A2:A20, B:B, or a table column. The function checks each cell in that range and adds one to the result when the cell matches the criterion. It returns a single number, not a list of matching cells.
The range is not the criterion itself, nor is it necessarily the whole worksheet column. If a list of order statuses is in column A, for example, the range should identify the status cells—not a nearby column containing dates or amounts. A well-chosen range makes the formula easier to understand and reduces the chance that headings or unrelated values are counted.
How to write a basic COUNTIF formula
The general argument order is COUNTIF(range, criteria): first specify where Excel should look, then what it should look for. In Microsoft 365 for Windows, click the result cell, type the formula, and press Enter. You can type a reference or select the cells with the mouse while entering the formula.
Assume A2:A8 contains Ready, Pending, Ready, Ready, Closed, Pending, and Ready, in that order. To count the Ready entries, use =COUNTIF(A2:A8,"Ready"). The result is 4. The text criterion is enclosed in straight double quotation marks. Excel does not require text criteria to match capitalization, so “ready” would also match these entries.
For a numeric test, assume B2:B8 contains 45, 120, 88, 150, 100, 75, and 220. To count values at least 100, use =COUNTIF(B2:B8,">=100"). The result is 4: 120, 150, 100, and 220 meet the condition. Comparison operators such as greater than and less than must be included in a text criterion, which is why the operator and number appear inside quotation marks.
Choosing the right cells for the range
For a small list, select the cells that contain the data and leave out the header. If the entries are in A2:A100, that exact reference is usually clearer than selecting all of column A. A bounded range also avoids counting a column heading that happens to match the criterion.
- Use a cell range such as A2:A100 when the data has a known start and end.
- Use a whole-column reference such as A:A when new entries may be added below the current list and the heading will not match the criterion.
- Use a table column when the data is an Excel table that should expand as rows are added.
Whole-column references are convenient, but Excel may need to consider many cells when recalculating a workbook. A few COUNTIF formulas are rarely a concern, but many formulas across large workbooks can be more efficient with appropriately sized ranges or tables. To use a table column, assume the table is named Sales and has a column named Status; =COUNTIF(Sales[Status],"Open") counts Open entries in that column. Table references generally adjust automatically as table rows are added.
Criteria: text, numbers, and cell references
The criterion can be typed into the formula or supplied by a cell. A cell reference is useful when someone needs to change the value being counted without editing the formula. Assume A2:A100 contains department names and D2 contains West; =COUNTIF(A2:A100,D2) counts the cells matching the current value in D2.
For a comparison that uses a value in another cell, join the operator to the cell reference with an ampersand. Assume B2:B20 contains amounts and E2 contains the threshold 100; =COUNTIF(B2:B20,">="&E2) counts amounts greater than or equal to the threshold. The operator is in quotation marks, while the threshold comes from E2. If E2 changes to 250, the count updates accordingly.
COUNTIF can also match a number exactly, such as =COUNTIF(B2:B20,100), or text exactly as a criterion, such as =COUNTIF(A2:A20,"West"). Text matches are not case-sensitive: COUNTIF does not distinguish “West” from “WEST.” If capitalization must be significant, COUNTIF alone is not the right function; a case-sensitive approach requires a different formula.
Wildcards and matching part of a cell
COUNTIF supports two wildcards in text criteria. An asterisk (*) stands for any number of characters, including no characters; a question mark (?) stands for exactly one character. These are useful when entries contain extra words or when a label has a predictable pattern.
Assume A2:A6 contains Apple, Apricot, Application, Pineapple, and Pear. To count entries beginning with “Ap,” use =COUNTIF(A2:A6,"Ap*"). The result is 3. The wildcard at the end lets any characters follow the letters Ap. To count three-character entries beginning with “P,” a criterion such as P?? would require exactly two more characters after P.
To search for a literal asterisk or question mark rather than use it as a wildcard, precede it with a tilde. If A2:A20 contains labels and some cells consist of the literal asterisk character, =COUNTIF(A2:A20,"~*") counts cells equal to that character. This distinction can matter when data includes codes or symbols.
Counting dates and blank cells
Excel stores dates as serial numbers, so COUNTIF can apply ordinary comparison operators to dates. Use a real Excel date in a cell or construct a date with DATE; avoid relying on ambiguous date text, which can be interpreted differently under different regional settings. Assume C2:C5 contains January 1, January 15, February 1, and February 12, 2026, and F2 contains January 15, 2026. The formula =COUNTIF(C2:C5,">="&F2) returns 3 because three dates are on or after the date in F2.
COUNTIF can also count empty-looking or nonempty cells. Assuming A2:A20 is the range being checked, =COUNTIF(A2:A20,"") counts blank cells, while =COUNTIF(A2:A20,"<>") counts cells that are not blank. The second criterion uses the not-equal operator. Cells containing formulas that return an empty string can behave like empty results for these tests, so check the underlying data if a blank count seems unexpected.
Keeping the range current as data grows
A fixed reference such as A2:A100 will not include a new entry typed in A101. You can extend the range when needed, use a whole-column reference, or convert the list into an Excel table. To make a table in Microsoft 365 for Windows, select the data and choose Insert > Table, confirming whether the data has headers. Give the table and its columns meaningful names if you plan to use structured references.
Tables are often a practical choice for ongoing lists because formulas can refer to a named column and the table expands when data is added directly beneath it. A fixed range can be preferable for a stable dataset or when you need tightly controlled calculation boundaries. Whichever method you use, make sure the range contains the records you intend to count and not totals, notes, or headings that could affect the result.
COUNTIF compared with COUNTIFS
COUNTIF handles one condition over one range. When a count must meet two or more conditions, use COUNTIFS, which pairs each criteria range with a criterion. For example, to count rows where the region is West and the amount is at least 100, assume A2:A5 contains West, East, West, West and B2:B5 contains 120, 150, 90, 200. Use =COUNTIFS(A2:A5,"West",B2:B5,">=100"). The result is 2: the first and fourth rows meet both tests.
In COUNTIFS, the criteria ranges should cover corresponding rows and have the same dimensions. If one range starts at row 2 and another at row 3, the conditions will not describe matching records as intended. COUNTIFS is not simply a way to put several unrelated tests into COUNTIF; each pair of ranges and criteria describes a condition that must be satisfied for the row to count.
Common range and result problems
- The range includes a heading. Usually start at the first data row, not the column label. A heading may be counted if it matches the criterion.
- The range stops too early. If recently added records fall below the selected cells, they are not included. Expand the reference or use a table or whole column.
- Numbers are stored as text. Imported values that look numeric may be text, which can make numeric criteria behave unexpectedly. Check the source data and convert values to numbers if appropriate.
- Spaces or inconsistent labels interfere. “West” and “West ” are different text strings. Clean inconsistent source entries if a count is lower than expected.
- The criterion has a typo or misplaced quotes. Text needs quotation marks when entered directly, and criteria that combine an operator with a cell reference need the ampersand.
- A formula shows an error or stale result. Check that the referenced cells exist, the formula is entered in a normal worksheet cell, and calculation is not set to Manual. In Microsoft 365 for Windows, calculation options are under Formulas > Calculation Options.
A useful check is to filter the source column for the same value or condition and compare the visible matching rows with the COUNTIF result. This can reveal hidden spaces, unexpected data types, or an incorrectly selected range. Filtering is a diagnostic check; it does not change what cells the formula references.
Frequently Asked Questions
What does range mean in an Excel COUNTIF formula?
The range is the set of cells COUNTIF examines, such as A2:A100. The second argument is the criterion that determines which cells in that range are counted.
Can COUNTIF use an entire column as its range?
Yes. A reference such as A:A is allowed and can include future entries, but it also includes the heading and every other cell in that column. Use it when that behavior is suitable for the data.
Can COUNTIF count cells based on a value in another cell?
Yes. Put the cell reference in the criteria argument. For comparisons such as greater than or equal to a cell value, join the operator to the reference with an ampersand.
Does COUNTIF distinguish uppercase and lowercase text?
No. COUNTIF text matching is not case-sensitive. “OPEN,” “Open,” and “open” are treated as matching text for the same criterion.
Why is my COUNTIF result zero?
Common causes include selecting the wrong range, a text value with extra spaces, a number stored as text, or a criterion that does not match the source data. Compare the criterion with the actual cell contents and inspect the range boundaries.
Related
- Excel to PowerPoint Link: How to Keep Slides Updated
- Excel to PowerPoint Linked Cells: How to Link and Update
- Convert Excel to PowerPoint: Methods, Steps, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP Partial Match: Wildcard Examples in ExcelFor a contains-style partial match, put the text to find in E2 and use =XLOOKUP("*"&E2&"*",A2:A7,B2:B7,"No match",2). The final…
- Average in Excel Between Two Values: Formula & TipsTo average numbers between two values in Excel, use AVERAGEIFS with a lower-bound and an upper-bound condition. For…
- EDATE in Excel: Add Months, Not DaysEDATE in Excel moves a date by whole calendar months; it does not add a specified number of…
- Excel Formula for High and Low Values: MIN and MAXIn Excel, use =MIN(B2:B10) to find the lowest number in a range and =MAX(B2:B10) to find the highest.…
- Match Excel Formula for Two Columns: Compare ValuesTo compare two cells on the same row in Excel, enter =IF(A2=B2,"Match","Different") in C2 and fill it down.…
- MATCH Excel String: Find Exact Text MatchesTo find an exact text string in Excel, use MATCH with its third argument set to 0. With…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.