Excel’s COUNTIF function counts cells in a range that meet one condition. Its basic pattern is =COUNTIF(range, criteria): specify where Excel should look and what each cell must match, and the function returns a number.
What COUNTIF Does
COUNTIF is useful when you need to count matching entries rather than add their values. For example, it can tell you how many orders are marked “Shipped,” how many scores exceed a threshold, or how many cells are empty. The count updates when the cells or the condition change, so it can support a summary that stays current as data is added or edited.
COUNTIF checks the cells in one range against one criterion. A criterion can be a value, text, a comparison such as “at least 10,” or a pattern using wildcards. COUNTIF returns one count; it does not list the matching cells, total their values, or apply different conditions to different columns.
For example, with order statuses in A2:A10, counting the cells that exactly match the text Shipped returns 4 if four cells contain that status:
With statuses in A2:A10, use: =COUNTIF(A2:A10,"Shipped"). If four cells contain Shipped, the result is 4.
Syntax and the Two Arguments
The first argument, range, is the group of cells Excel checks. It can be a selected block such as A2:A100 or a whole column such as A:A. The second argument, criteria, tells Excel which cells to count. The two arguments are separated by a comma in the formulas shown here.
COUNTIF takes one range and one criterion. That makes it a straightforward choice for a single test, but it also sets a limit: it cannot, by itself, require a row to meet conditions in two separate columns. The range should contain the values you intend to test, not a different field whose contents happen to be related.
For a simple numeric test, suppose quantities are in B2:B20 and the goal is to count cells equal to 100. The formula returns 3 if exactly three cells have that value:
With quantities in B2:B20, use: =COUNTIF(B2:B20,100). If three entries equal 100, the result is 3.
Writing Criteria for Text and Numbers
A criterion can match a particular text value or number. Text criteria go in double quotation marks, as in the Shipped example. A numeric criterion can be entered as a number without quotation marks. To use a comparison operator, include the operator and value together inside quotation marks. Excel recognizes operators including equals, greater than, less than, greater than or equal to, and less than or equal to.
For instance, to count scores of at least 70 in B2:B20, put the comparison in the criterion. If four scores are 70 or higher, the count is 4:
With scores in B2:B20, use: =COUNTIF(B2:B20,">=70"). If four scores meet the threshold, the result is 4.
If the threshold is stored in another cell, join the operator text to that cell reference with the ampersand (&). This lets you change the threshold without editing the formula. Suppose scores are in B2:B20 and the threshold is in E2; if E2 contains 70, the count is again 4 when four scores qualify:
With scores in B2:B20 and the threshold in E2, use: =COUNTIF(B2:B20,">="&E2). If E2 is 70 and four scores are at least 70, the result is 4.
Quotation marks are a frequent source of errors. When an operator and a fixed value make up the criterion, both belong inside the quoted text, as in ">=70". When the value comes from a cell, the operator stays in quotes and the cell reference is joined outside them. Do not put the cell reference itself in quotes, or Excel will treat it as text rather than read its value.
Using Wildcards to Match Text Patterns
COUNTIF supports wildcards in text criteria. The asterisk (*) stands for any number of characters, including none. The question mark (?) stands for exactly one character. These are useful when names, codes, or descriptions are not identical but share a pattern.
- To count cells whose text begins with North, a criterion of
"North*"also matches entries such as Northside or North Region. - To count five-character codes beginning with A, a criterion of
"A????"matches A followed by exactly four characters. - To count a cell containing a literal asterisk rather than using the wildcard, put a tilde before it in the criterion. A tilde also escapes a question mark or another tilde.
For example, if product labels are in A2:A20 and two labels begin with North, the formula returns 2:
With product labels in A2:A20, use: =COUNTIF(A2:A20,"North*"). If two labels start with North, the result is 2.
COUNTIF text matching is not case-sensitive: criteria for North and NORTH count the same capitalization variants. If a task genuinely requires distinguishing uppercase and lowercase letters, COUNTIF alone is not the right tool; a case-sensitive approach using other functions is needed.
Counting Dates and Date Ranges
Excel stores dates as serial numbers, even though cells are usually formatted to display calendar dates. As a result, COUNTIF can compare dates using the same operators it uses for numbers. The cells being checked need to contain real Excel date values, not text that merely looks like a date.
If E2 contains a real date and the dates to check are in C2:C100, an exact-match count can use E2 as the criterion. If that date appears three times, the result is 3:
With dates in C2:C100 and the target date in E2, use: =COUNTIF(C2:C100,E2). If the date appears three times, the result is 3.
For dates on or after a boundary date in E2, join the comparison operator to the cell reference. If four dates meet that test, the formula returns 4:
With dates in C2:C100 and the boundary date in E2, use: =COUNTIF(C2:C100,">="&E2). If four dates are on or after E2, the result is 4.
To count dates within a bounded period, such as from a start date through an end date, use COUNTIFS, which allows multiple criteria. Make sure the start and end cells contain dates and decide whether each boundary should be inclusive; the operators determine that. Text-formatted dates can cause surprising results because they may not compare like true date values.
Counting Blank and Nonblank Cells
A blank criterion is useful for finding missing entries. With a criterion of an empty text string, COUNTIF counts cells that are blank; this can include cells containing formulas that display an empty string. For example, if A2:A20 contains a list with three blank-looking cells, the result is 3:
With entries in A2:A20, use: =COUNTIF(A2:A20,""). If three cells are blank or display an empty string, the result is 3.
To count cells that are not blank, use the not-equal operator with an empty criterion. If there are 16 nonblank entries in the same 19-cell range, the count is 16:
With entries in A2:A20, use: =COUNTIF(A2:A20,"<>"). If 16 cells are nonblank, the result is 16.
Be clear about what “blank” means in your data. A cell containing a space is not empty, even if it looks blank on screen. A formula that returns an empty string may also behave differently from a truly unused cell in other counting functions. If a count seems off, inspect the cells and check whether they contain spaces or formulas.
Choosing a Range and Keeping Formulas Reliable
Use a range that includes the records you intend to count and excludes headings when a heading could match the criterion. In a growing worksheet, a whole-column reference can include new entries automatically, while a fixed block makes the checked area explicit. Structured table references are another option when the data is formatted as an Excel table; they can make formulas easier to read as the table expands.
When copying a formula, consider whether its range should move. Ordinary references such as A2:A20 adjust when copied to another location. Dollar signs make references absolute or partially absolute. For example, a criteria cell written as $E$2 stays fixed when the formula is copied; a relative reference moves with it. Choose references according to how the formula will be filled, rather than adding dollar signs automatically.
COUNTIF does not limit its count to visible rows. If a filter hides some rows, the function still counts matching cells in the referenced range, including filtered-out rows. That distinction matters in reports where “count the visible results” is the intended question; COUNTIF is not a visibility-aware count.
Common COUNTIF Problems
- The result is zero unexpectedly. Check for extra spaces, spelling differences, numbers stored as text, or dates stored as text. Confirm that the selected range contains the values and that the criterion has the intended wording.
- A comparison is written incorrectly. Put a fixed operator and value together in quotation marks, such as
">10". For an operator paired with a cell reference, concatenate them with&. - The count is too high for a text pattern. An asterisk or question mark in a criterion acts as a wildcard. If you mean a literal wildcard character, escape it with a tilde.
- The formula changes when copied. Check whether references should be relative or locked with dollar signs. Also confirm that a copied formula still points to the intended data range.
- A filtered list shows a different visible count. COUNTIF includes hidden and filtered-out cells. Its result is a count across the specified range, not just the rows currently displayed.
In Windows Excel, a formula can be entered in a cell and confirmed with Enter. If Excel displays the formula as text instead of calculating it, check that the cell is not formatted as Text and that the entry begins with an equals sign. Excel for Mac and Excel for the web use the same COUNTIF function and formula pattern, although keyboard shortcuts and some interface details differ by platform.
COUNTIF, COUNTIFS, and Related Functions
Use COUNTIF when one condition is enough. Use COUNTIFS when a count must satisfy multiple conditions, such as a status in one column and a date boundary in another. COUNTIFS pairs each criteria range with its own criterion, so the ranges should correspond to the same records and have matching dimensions.
For example, suppose statuses are in A2:A20, regions in B2:B20, and the goal is to count shipped orders in the West region. If three rows meet both conditions, COUNTIFS returns 3:
With statuses in A2:A20 and regions in B2:B20, use: =COUNTIFS(A2:A20,"Shipped",B2:B20,"West"). If three rows meet both conditions, the result is 3.
Do not confuse COUNTIF with SUMIF. COUNTIF returns how many cells meet a condition; SUMIF adds values associated with a condition. If the question is “How many orders are over $100?” COUNTIF may be appropriate. If it is “What is the total value of orders from the West?” SUMIF or SUMIFS is the more relevant function.
Frequently Asked Questions
Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase versions of text as matching. A case-sensitive count requires a different formula approach.
Can COUNTIF use more than one condition?
No. COUNTIF accepts one criterion. Use COUNTIFS to count records that meet two or more conditions.
Why does COUNTIF return zero for a date?
The compared cells or the criterion may contain text rather than real Excel dates, or the displayed dates may include different underlying times. Check the stored values and use compatible date values and boundaries.
Does COUNTIF count filtered-out rows?
Yes. It evaluates the referenced range regardless of whether rows are hidden by a filter. It does not count only visible matches.
Can I use a cell reference as the criterion?
Yes. Use the reference directly for an exact match. For a comparison, join the operator text to the reference with an ampersand so Excel reads the value in that cell.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF tests one condition; COUNTIFS can test multiple conditions, often across different columns. Use the simpler function when only one test is needed.
Related
- Excel Unmerge Cells Shortcut: Keys, Steps, and Fixes
- Excel Unmerge Cells and Fill Every Cell with Its Data
- Excel Undo Shortcut: Ctrl+Z and How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort Excel by Color: Cells, Font Colors, and IconsTo sort Excel rows by color in Microsoft 365 for Windows, select your data, choose Data > Sort,…
- Excel Filter Numbers From Text: Formulas and StepsTo filter Excel rows where a text cell contains one or more digits, add a helper column with…
- Formula for Overtime Hours in Excel: Simple ExamplesFor a daily overtime calculation in Excel, use =MAX(0,(MOD(C2-B2,1)-D2)*24-E2). With a start time in B2, end time in…
- Excel Formula to Split Text by Delimiter: How It WorksIn Microsoft 365 for Windows, split text with =TEXTSPLIT(A2,","), replacing A2 with the cell that contains your text…
- Excel Biweekly Timesheets: Setup and Formula GuideYes. Excel can track biweekly timesheets by recording each shift in a 14-day date range, calculating hours, and…
- Excel SEQUENCE Function for Dates: Examples and TipsIn Excel for Microsoft 365, use =SEQUENCE(7,1,DATE(2026,4,1),1) to generate seven consecutive date values starting April 1, 2026. Format…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.