COUNTIF Excel Criteria Examples and How to Use Them

Excel’s COUNTIF function counts cells that meet one condition. With A2:A9 containing Paid, Pending, Paid, Overdue, paid, Pending, Paid, and Overdue, use =COUNTIF(A2:A9,"Paid"); the result is 4 because COUNTIF is not case-sensitive.

How COUNTIF criteria work

COUNTIF checks each cell in a range against a criterion and returns the number of matches. It is useful for questions such as how many orders are pending, how many scores exceed a threshold, or how many cells contain a particular word. The function has two arguments: the range to inspect and the criterion to apply. Excel’s function syntax is COUNTIF(range, criteria); in a worksheet formula, replace those descriptions with a real cell range and a criterion.

A criterion can be a number, text, comparison expression, wildcard pattern, or a reference to a cell containing the condition. Text criteria and operators such as greater than are usually enclosed in quotation marks. A criterion held in a cell is generally referenced without quotes. COUNTIF checks one condition per formula, so it is a good fit when a single rule is enough.

Exact text and number examples

For a basic text match, suppose A2:A9 contains the eight statuses listed in the opening example. The formula =COUNTIF(A2:A9,"Paid") returns 4. Excel treats Paid, PAID, and paid as the same text for COUNTIF, so capitalization does not make the match exact. Extra spaces are different: a cell containing “Paid ” with a trailing space may not match the criterion “Paid”.

For a numeric comparison, suppose B2:B11 contains 12, 18, 25, 25, 31, 40, 25, 18, 52, and 60. To count values of 25 or higher, use =COUNTIF(B2:B11,">=25"). The result is 7: three cells contain 25, and four contain a larger value. The comparison operator and number form one quoted criterion.

To count values exactly equal to a number, the number can be used directly as the criterion. With the same B2:B11 values, =COUNTIF(B2:B11,25) returns 3. Common comparison operators include >, <, >=, <=, and <> (not equal to). Put the operator and its value together inside quotation marks when writing the criterion directly.

Using cell references as criteria

A cell reference makes a condition easier to change without editing the formula. Suppose the numbers are in B2:B11 and the threshold is in D2, where D2 contains 25. The formula =COUNTIF(B2:B11,D2) returns 3 because it counts values equal to 25. Change D2 to 18 and the result becomes 2.

For a comparison using a referenced threshold, join the operator in quotes to the cell reference with the ampersand. With the same B2:B11 values and 25 in D2, =COUNTIF(B2:B11,">="&D2) returns 7. The ampersand combines the text operator with the value in D2 to create the criterion. This pattern is useful for dashboards, cutoff settings, and worksheets where users may adjust the threshold.

When copying a formula, consider whether the range should move. If the range must stay fixed while the formula is copied, use absolute references, such as $B$2:$B$11. The dollar signs lock the row and column references; they do not change what COUNTIF counts.

Wildcards for partial text matches

COUNTIF supports two wildcards in text criteria. An asterisk (*) represents any number of characters, including no characters; a question mark (?) represents exactly one character. These are helpful when entries vary but share a recognizable part.

Starts with: Suppose A2:A7 contains Maple, Market, March, Marigold, Apricot, and Marble. =COUNTIF(A2:A7,"Mar*") returns 4 because four entries begin with Mar.
Contains: With the same values, =COUNTIF(A2:A7,"*ap*") returns 2: Maple and Apricot contain the letters ap without regard to case.
One-character variation: Suppose A2:A5 contains AX1, AX2, AX10, and AY1. =COUNTIF(A2:A5,"AX?") returns 2 because ? matches one character, so AX1 and AX2 qualify but AX10 does not.

If the text you need to find contains an actual asterisk or question mark, place a tilde (~) before that character to treat it as a literal rather than a wildcard. For example, with product codes in A2:A10, =COUNTIF(A2:A10,"~*sale") counts cells whose text ends with a literal asterisk followed by sale. Wildcards are designed for text matching; they are not a general way to search inside numeric values.

Counting blanks and nonblank cells

To count empty cells in A2:A20, use =COUNTIF(A2:A20," ") only if you mean a space character. For genuinely blank cells, the criterion should be an empty string: with the same range, =COUNTIF(A2:A20,"") counts cells Excel treats as blank. This can include cells containing formulas that return an empty string, so the result may be larger than the number of cells that have never contained anything.

To count cells that are not blank, use =COUNTIF(A2:A20,"<>"). This is useful for checking how much of a list has been filled in. A cell containing a space is not truly empty, even if it looks blank on screen. If blank counts seem surprising, inspect the cells for spaces or formulas that return an empty string.

Dates and date ranges

Excel stores dates as serial numbers, but a date criterion is easier to read and maintain when it is constructed with DATE. Suppose order dates are in C2:C20. To count dates on or after January 1, 2026, use =COUNTIF(C2:C20,">="&DATE(2026,1,1)). The result depends on the dates in C2:C20; the formula includes the start date itself.

COUNTIF applies one condition, so it cannot by itself express both a start date and an end date as two separate limits. For dates within a month, COUNTIFS is usually more appropriate. If C2:C20 contains dates, =COUNTIFS(C2:C20,">="&DATE(2026,1,1),C2:C20,"<"&DATE(2026,2,1)) counts dates in January 2026, including January 1 and excluding February 1. Using a less-than next-month boundary also works when date cells include times later in the final day.

For date criteria to work reliably, the cells need to contain actual Excel dates rather than text that merely looks like a date. If a date comparison gives an unexpected result, check the cell format and whether the value is stored as a date or as text.

Choosing between COUNTIF and related functions

Use COUNTIF when one condition is enough. Use COUNTIFS when every counted row must satisfy two or more conditions. For example, suppose region names are in A2:A20 and order statuses are in B2:B20. The formula =COUNTIFS(A2:A20,"East",B2:B20,"Paid") counts rows where the region is East and the status is Paid. The result depends on those rows. Each criteria range in COUNTIFS should cover the corresponding data rows so the conditions are evaluated against matching records.

For an either/or question, such as counting rows marked Paid or Pending in one status column, add two COUNTIF results. With statuses in A2:A20, =COUNTIF(A2:A20,"Paid")+COUNTIF(A2:A20,"Pending") returns the combined number of those two types of entries. This is different from COUNTIFS, which applies multiple conditions together rather than offering an either/or test on the same criterion.

COUNT, COUNTA, and COUNTBLANK answer different questions. COUNT counts numeric values, COUNTA counts nonempty cells, and COUNTBLANK counts blank cells. Choose COUNTIF when the count depends on a specific rule, such as a particular status or a numeric cutoff.

Common COUNTIF problems and fixes

Numbers stored as text: A cell may look numeric but be stored as text, which can lead to confusing comparisons. Check how the values were imported and convert them to numbers if numeric comparisons are intended.
Hidden spaces: Leading or trailing spaces can prevent an exact text match. TRIM can remove ordinary extra spaces in a cleaned copy of the data; it is often better to fix the source data than to make the criterion increasingly complicated.
Incorrect quotes or operators: A criterion such as >=25 needs the operator and value together when typed directly as text. Check that quotation marks are straight double quotes and that the operator is in the intended order.
Wrong range: Confirm that the range covers the records you meant to include and does not accidentally omit the last row or include a heading. When copying formulas, check whether relative references shifted.
Dates stored as text: Text dates may not compare as expected with real Excel dates. Convert the source values to Excel dates before relying on date criteria.
Wildcard interpreted literally: Remember that * and ? have special meanings in text criteria. Use a tilde before either symbol when the symbol itself is part of the text to find.

In Microsoft 365 for Windows, enter the formula in a cell and press Enter. If your Excel regional settings use semicolons as argument separators, Excel may display or require semicolons instead of commas; the examples here use the standard comma form. Excel for Mac and Excel for the web use the same COUNTIF logic, though separators can also depend on regional settings.

Frequently Asked Questions

Is COUNTIF case-sensitive?
No. COUNTIF treats upper- and lowercase versions of text as equivalent. If a task requires a case-sensitive count, COUNTIF alone is not the right tool.

Can COUNTIF use more than one condition?
No. COUNTIF applies one criterion. Use COUNTIFS when a record must meet multiple conditions, such as matching both a region and a status.

How do I count cells that contain a word anywhere in the text?
Put an asterisk before and after the word in the criterion. For example, with notes in A2:A20, =COUNTIF(A2:A20,"*late*") counts cells containing late anywhere in their text, without regard to capitalization.

Why does COUNTIF return zero when the text looks right?
Check for extra spaces, a misspelled criterion, or data stored differently than expected. COUNTIF ignores capitalization, but it does not ignore added characters such as a trailing space.

Can COUNTIF count dates in a month?
A month is bounded by a start date and an end date, which are two conditions. Use COUNTIFS with a greater-than-or-equal-to start and a less-than next-month date, as in the January example above.

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.