Excel COUNTIF: How to Count Matching Cells

Formulas & functions · Updated November 20, 2026

In Excel, use COUNTIF to count cells in a range that meet one condition: enter =COUNTIF(range, criteria). For example, if A2:A8 contains region names, =COUNTIF(A2:A8,"North") counts the cells equal to North.

What COUNTIF does

COUNTIF is useful when you want a count, rather than a total or an average, based on a rule. It can answer questions such as how many orders have a particular status, how many scores meet a threshold, or how many entries contain a word. Excel checks each cell in the range against the criterion and returns one number: the number of cells that match.

It is a single-condition function. The comparison is not case-sensitive, so North and NORTH are treated as the same text for COUNTIF. The function does not change or filter the underlying data; it simply reports a count, which updates when the referenced cells change.

COUNTIF is available in Microsoft Excel for Microsoft 365 on Windows, Mac, and the web. The basic formula is the same in these versions. In a regional installation that uses semicolons as formula separators, Excel may require semicolons instead of commas; the examples here use the standard comma-separated syntax.

Understand the syntax and criteria

The first argument is the range Excel should inspect. The second is the criterion: the condition a cell must satisfy to be counted. A criterion can be a value, text, a comparison such as greater than a number, a wildcard pattern, or a reference to another cell.

For example, a text criterion can be written as "North", while a numeric comparison is written as ">100". Put comparison operators and text criteria in quotation marks. If a criterion is stored in another cell, refer to that cell instead of typing its value into the formula. When combining a comparison operator with a cell reference, join them with the ampersand (&) operator.

Keep the range consistent in shape and size. COUNTIF is generally used with one row or one column of data, such as A2:A100. If you want to count records only when multiple conditions are true—for example, a region and a minimum order amount—use COUNTIFS instead.

Enter your first COUNTIF formula

To build a basic count in Excel for Microsoft 365 on Windows, select the cell where you want the result, type the formula, and press Enter. The result cell can be beside the data or on a summary sheet. The formula will recalculate when values in the source range change.

  1. Identify the column or row containing the values to check.
  2. Select an empty cell for the count.
  3. Type COUNTIF with the range and the condition, then press Enter.
  4. Check the returned number against the data, especially if the range includes a header or extra rows.

With the region names North, South, North, East, North, South, and West in A2:A8, enter =COUNTIF(A2:A8,"North") in the result cell. The result is 3 because North appears three times. The range starts at row 2 so the example does not include a column heading in A1.

To reuse the formula as you add records, you can make the source range larger than the current data or convert the data to an Excel table. A table expands as records are added, and formulas that refer to table columns can be easier to understand. If you use a fixed range such as A2:A8, newly added values outside that range will not be counted until you update the reference.

Count text, words, and partial matches

For an exact text match, type the text in quotation marks as the criterion. COUNTIF ignores differences in capitalization, but spaces and spelling still matter. A cell containing North followed by an extra space may not match the criterion North. If data comes from an export or copy-and-paste, inconsistent spaces are a common reason a count seems too low.

Wildcards let you count text that follows a pattern. An asterisk (*) represents any number of characters, including no characters. A question mark (?) represents exactly one character. With the entries Ava Chen, Ava Patel, Mia Chen, Ava, Noah Chen, and Ava Chen in C2:C7, the formula =COUNTIF(C2:C7,"Ava*") returns 4: it matches all four entries that start with Ava, including the entry Ava by itself.

To count names ending in Chen, use a criterion such as "*Chen". To count text with exactly one character before a suffix, use ? for that single character. If the text you need to find includes a literal asterisk, question mark, or tilde, precede that character with a tilde in the criterion. For example, a tilde tells Excel to treat the following wildcard symbol as an ordinary character.

COUNTIF does not search for a phrase anywhere in a cell unless the criterion uses wildcards on both sides. A criterion like "*Chen*" matches Chen at the beginning, middle, or end of a text entry. Use this deliberately: broad patterns can include more records than an exact-match count.

Count numbers and dates with conditions

Use comparison operators to count values above, below, equal to, or different from a threshold. The operators include >, >=, <, <=, =, and <>. Because an operator is part of a text criterion, place it and the number together in quotation marks. For example, with the values 120, 85, 150, 95, 120, 200, and 70 in B2:B8, =COUNTIF(B2:B8,">=100") returns 4: the matching values are 120, 150, 120, and 200.

If the threshold is in a worksheet cell, join the operator to the reference. With the same values in B2:B8 and the number 100 in D1, =COUNTIF(B2:B8,">="&D1) also returns 4. This version is convenient when someone may change the threshold without editing the formula.

Dates can also be counted using comparison criteria, provided the cells contain real Excel dates rather than text that merely looks like a date. With the dates April 1, April 5, April 10, April 15, and April 20 of the same year in A2:A6, and a real Excel date for April 10 of that year in D1, =COUNTIF(A2:A6,">="&D1) returns 3. It counts April 10 and the two later dates. If the displayed dates are stored as text, convert them to date values before relying on date comparisons.

When to use COUNTIFS instead

COUNTIF accepts one condition. COUNTIFS counts rows or cells that meet two or more conditions, making it the better choice for questions such as “How many North orders are at least 100?” With North, South, North, East, North, South, and West in A2:A8, and 120, 85, 150, 95, 120, 200, and 70 in B2:B8, enter =COUNTIFS(A2:A8,"North",B2:B8,">=100"). It returns 3: all three North records have values of at least 100.

Each COUNTIFS range must cover the same number of rows and columns as the other criteria ranges. A mismatch can produce an error. If you only need one condition, COUNTIF is simpler and easier to read; if every condition must be true for a record to count, use COUNTIFS. COUNTIFS applies AND logic between its criteria, not OR logic.

For an OR question, such as counting North or South, one straightforward approach is to add two separate COUNTIF results. Be careful if a record can satisfy both conditions, because adding counts for overlapping conditions can count that record more than once. Choose the method based on whether the categories are mutually exclusive.

Common COUNTIF problems and fixes

  • The result is zero unexpectedly: Check spelling, leading or trailing spaces, and whether the criterion is written as text in quotation marks. Confirm that the formula points to the rows containing data, not just the heading.
  • A comparison gives the wrong count: Check that the operator and threshold are together in the criterion, such as ">=100". If the threshold comes from a cell, join the operator and reference with &.
  • New rows are missing: A fixed reference such as A2:A8 does not expand automatically just because data is entered in A9. Extend the range or use a table column reference that grows with the table.
  • Numbers or dates do not behave as expected: Values stored as text may not compare like numeric values or real dates. Check the source data and convert text values where necessary.
  • The count changes when a formula is copied: Relative references move when copied. Use dollar signs to lock a range if needed; for example, $A$2:$A$100 stays fixed when copied to another cell.

When debugging, test a small, known range first and inspect the cells that should match. You can also click the formula cell and examine the formula bar to verify the range and criterion. COUNTIF does not distinguish upper- and lowercase text, so if case-sensitive counting is essential, COUNTIF alone is not sufficient.

Use COUNTIF in a practical summary

A simple summary area can turn a long list into a quick status report. For an order log, you might count each status in a separate summary row: Pending, Shipped, and Cancelled. Each formula checks the same status column but uses a different criterion. If you store the status labels in summary cells, reference those cells as criteria so the formula can be copied down and the label determines each count.

For example, if statuses are in A2:A100 and the first summary label is in D2, the formula =COUNTIF($A$2:$A$100,D2) counts the status named in D2. With Pending in D2 and 12 Pending entries in the source range, the result is 12. The dollar signs keep the source range fixed when you copy the formula to the next summary row; D2 remains relative and changes to D3, D4, and so on.

For a frequently updated log, consider formatting the source as a table and giving its columns clear names. This improves readability and helps formulas track expanding data. Whichever layout you choose, check whether blank rows, totals, or notes are inside the counted range; they can affect results if the criterion matches them.

Frequently Asked Questions

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is not case-sensitive, so North and NORTH count as the same text. A case-sensitive count requires a different approach rather than changing the capitalization of the COUNTIF criterion.

How do I count cells that contain a word anywhere?
Use an asterisk before and after the word in the criterion. For instance, a pattern like *late* can match text with late anywhere in the cell. A broad wildcard pattern may also match longer words containing those letters, so review whether that is appropriate for your data.

Can COUNTIF count values greater than a number in another cell?
Yes. Combine the comparison operator in quotation marks with the cell reference using &. For example, with values in B2:B8 and a threshold in D1, use =COUNTIF(B2:B8,">"&D1) to count values greater than the threshold.

Why does COUNTIF return zero for a date?
The apparent dates may be text rather than Excel date values, or the criterion may not be constructed correctly. Confirm that the source cells and any threshold cell contain real dates, then use a comparison criterion joined to the date-cell reference.

Can I use COUNTIF for two conditions?
Not in one COUNTIF function. Use COUNTIFS when both conditions must be met. For alternatives such as one condition or another, separate counts may work, but check for overlap so records are not counted twice.

Related

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.