COUNTIF Examples: Formulas for Common Spreadsheet Tasks

Use Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it can count a particular word, values above a threshold, dates after a cutoff, or cells containing a matching text pattern.

What COUNTIF Does

COUNTIF is useful when you want a count rather than a sum or a list of matching records. It checks each cell in a range against one criterion and returns the number of cells that satisfy it. The function is available in Microsoft Excel for Microsoft 365 on Windows, as well as current Mac and web versions.

The syntax has two arguments: =COUNTIF(range,criteria). The range is the group of cells to examine; the criteria is the rule each cell must match. Criteria can be a number, text, a comparison such as greater than 10, a wildcard pattern, or a reference to another cell. Put text and comparison criteria in straight double quotation marks when entering them directly in a formula.

COUNTIF does not add the values it finds, and it does not return the matching cells. For a sum based on a condition, look at SUMIF; for several matching conditions, use COUNTIFS. Those distinctions help avoid asking COUNTIF to do a job it was not designed for.

Count Exact Text

Suppose a worksheet records a department in each row. To count how often the department name North appears in A2:A20, use this formula:

With department names in A2:A20, and North appearing in six cells, enter =COUNTIF(A2:A20,"North"). The result is 6.

Text matching is not case-sensitive: North, NORTH, and north are treated as the same criterion. COUNTIF looks for a cell whose value matches the specified text; it does not require the text to be visibly formatted in a particular way. Extra spaces can still cause a mismatch. A cell containing North with a trailing space may not match the criterion North.

For a condition stored in a cell, use that cell as the criterion. With department names in A2:A20 and the desired department in E2, enter =COUNTIF(A2:A20,E2). If E2 contains North and six records match, the result is 6. This makes a report easier to reuse: change E2 to another department instead of editing the formula.

Count Numbers and Apply Thresholds

A number can be an exact criterion, or a comparison operator can define a range of values. For example, a sales sheet might need a count of orders worth at least 100.

With order values in B2:B20 and six values equal to or greater than 100, enter =COUNTIF(B2:B20,">=100"). The result is 6. The operator and value go together inside quotation marks. Common operators include > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), and <> (not equal to).

To count cells that are not zero, use a not-equal criterion. With quantities in B2:B20 and 17 cells containing a nonzero value, enter =COUNTIF(B2:B20,"<>0"). The result is 17. If the cutoff is in another cell, join the operator to that reference with an ampersand. With values in B2:B20 and a threshold in E2, enter =COUNTIF(B2:B20,">="&E2). If E2 contains 100 and six values meet the threshold, the result is 6.

Count Dates Before or After a Cutoff

Excel stores real dates as numbers, so COUNTIF can compare them using the same operators used for numeric values. Make sure the cells contain actual Excel dates, not text that merely looks like a date. Date display formats do not change the underlying value used in the comparison.

With event dates in C2:C100, and 12 dates on or after January 1, 2026, enter =COUNTIF(C2:C100,">="&DATE(2026,1,1)). The result is 12. The DATE function builds a date from year, month, and day, avoiding ambiguity about whether a typed date is interpreted as month/day or day/month. If the cutoff date is in E2 instead, with the same 12 dates on or after it, use =COUNTIF(C2:C100,">="&E2).

For timestamps that include a time of day, a cutoff at midnight may include or exclude rows differently than a reader expects. For example, “on or after January 1” includes timestamps from that date onward when the cutoff is January 1 at midnight. For counting all timestamps on one specific day, a pair of boundaries is often clearer; COUNTIFS can apply both conditions to the same date range.

Use Wildcards for Partial Text

Wildcards help count text when only part of the content is known. In Excel criteria, an asterisk (*) stands for any number of characters, including none, and a question mark (?) stands for exactly one character.

With product descriptions in A2:A50 and seven cells containing the word east anywhere in the text, use =COUNTIF(A2:A50,"*east*"). The result is 7.
With codes in A2:A50 and four codes that begin with AB, use =COUNTIF(A2:A50,"AB*"). The result is 4.
With identifiers in A2:A50 and five three-character values that start with A and end in 7, use =COUNTIF(A2:A50,"A?7"). The result is 5.

In each example, the stated range is the cell layout the formula assumes. A wildcard can be useful for categories embedded in descriptive text, but it can also match more than intended: *east* can match “Northeast” as well as “east region.” If a wildcard character itself is part of the text you need to find, precede it with a tilde (~) to treat it literally. For example, ~* represents an actual asterisk in a criterion.

Count Blank and Nonblank Cells

COUNTIF can count empty-looking cells, which is handy for finding missing entries in a simple list. With response cells in D2:D100 and 11 cells blank or returning an empty string, enter =COUNTIF(D2:D100,""). The result is 11. This criterion can match genuinely empty cells and cells whose formulas return an empty string.

To count cells that contain something, use a not-empty criterion. With the same response range and 89 cells that COUNTIF treats as nonblank, enter =COUNTIF(D2:D100,"<>"). The result is 89. Be mindful that “blank” can mean different things in a worksheet: a cell may look empty because it contains a formula returning "", or because its contents are hidden by formatting. When counts drive a report, check the cells themselves if the result seems unexpected.

Where One Condition Is Not Enough

COUNTIF accepts one criterion. It cannot directly express a rule such as “department is North and status is Complete” or “value is below 10 or above 100.” For multiple conditions that all need to be true, use COUNTIFS, which pairs each criteria range with its criterion.

With departments in A2:A20, statuses in B2:B20, and three rows where the department is North and the status is Complete, enter =COUNTIFS(A2:A20,"North",B2:B20,"Complete"). The result is 3. The criteria ranges must cover corresponding rows; keeping their sizes aligned makes the intended row-by-row test clear.

For an either/or count, two COUNTIF results can sometimes be added. But if a cell can meet both conditions, adding the counts may count that cell twice. For example, counting descriptions containing “red” or “large” needs care if some descriptions contain both words. Decide whether you want unique matching rows or the total of two separate category counts before choosing a formula.

Common COUNTIF Problems

Missing quotation marks: Text criteria and comparison expressions typed into a formula need quotation marks, such as "Complete" or ">=100". A cell reference used directly as a criterion, such as E2, does not need quotes.
Joining a cell reference incorrectly: To build a comparison using a cell value, use an ampersand, as in ">="&E2. Without the join, Excel may treat the criterion as plain text rather than the intended comparison.
Numbers stored as text: A number imported as text may not behave like a numeric value. Check the cell values and data import if a threshold count is unexpectedly low.
Unnoticed spaces: Leading or trailing spaces in imported text can stop an apparent exact match. Cleaning the source data or using a suitably broad wildcard may help, though broad matching can include unintended records.
Range drift after copying: Relative references shift when a formula is copied. If the same data range must remain fixed, use absolute references such as $A$2:$A$20.
Wrong function for the task: COUNTIF is case-insensitive and applies one condition. It is not a case-sensitive text counter or a multi-condition function.

When a result looks wrong, inspect a few cells that should match and a few that should not. Confirm the data type, hidden spaces, formula criterion, and selected range before assuming the function itself is faulty.

COUNTIF Compared with Related Functions

Choose the function by the question being asked, not just by the shape of the data. COUNTIF counts cells that meet one condition. COUNTIFS counts records meeting multiple conditions at once. SUMIF adds values associated with one condition, while SUMIFS supports multiple conditions. A filter can show matching records for inspection, but it does not replace a formula when you need a count that updates as the data changes.

For example, a request to count every order over 100 calls for COUNTIF. A request to count orders over 100 placed by North calls for COUNTIFS. A request to total the sales from North calls for SUMIF or SUMIFS. These functions may use similar criteria, but their results answer different questions.

Frequently Asked Questions

Can COUNTIF count cells that contain a word anywhere?
Yes. Use an asterisk on each side of the text, such as "*east*", as the criterion. This matches the text as part of a longer cell value, so review whether related words or names could also match.

Is COUNTIF case-sensitive?
No. COUNTIF does not distinguish uppercase from lowercase text. If a task truly requires case-sensitive counting, COUNTIF alone is not the right tool; a formula using other functions may be needed.

Can COUNTIF check two conditions?
Not in a single COUNTIF formula. Use COUNTIFS when both conditions must be true for the same row. If the logic is “this condition or that condition,” consider whether adding separate counts could double-count records.

Why does COUNTIF return zero for text that looks identical?
Common reasons include extra spaces, a mismatch between text and numbers, or a criterion that does not match the actual cell contents. Check the source cells and the exact criterion, including punctuation and any wildcard characters.

Does COUNTIF count truly empty cells?
Yes. A blank criterion, written as "", can count empty cells and cells with formulas returning an empty string. That means the result may include cells that look blank but are not physically empty.

Can I use a date cell as the cutoff?
Yes. Join the comparison operator to the date-cell reference, for example ">="&E2. Ensure E2 contains a real Excel date value, and not date-shaped text.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.