COUNTIF in Excel: How It Works and Examples

In Excel, COUNTIF counts cells in a range that meet one condition. Its syntax is =COUNTIF(range, criteria); for example, with regions in A2:A8, =COUNTIF(A2:A8,"East") counts how many cells contain East.

What COUNTIF Does

COUNTIF is a worksheet function for answering questions such as “How many orders are over $100?”, “How often does a category appear?” or “How many cells are blank?” It checks each cell in a specified range against one criterion and returns a number: the count of cells that match. It does not add the matching values or return the matching rows.

The function is useful for quick summaries and checks in lists, sales records, survey responses, attendance sheets, and other tables. You can type it directly into a cell or select it from Excel’s Formulas tab. The examples here are for Microsoft Excel for Microsoft 365 on Windows. They use commas between arguments, as Excel does with the standard English-language formula separators; some regional settings use semicolons instead.

COUNTIF Syntax and Criteria

The function has two arguments: range is the group of cells to inspect, and criteria is the condition each cell is tested against. The range can be a regular cell reference such as A2:A20. The criterion can be a number, text, comparison, wildcard pattern, or reference to another cell.

Text criteria and criteria containing comparison operators go in straight double quotation marks. A number by itself generally does not need quotation marks. Excel’s comparison operators include equal to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). When you put an operator inside a text criterion, the operator and value belong together inside the quotation marks.

COUNTIF is not case-sensitive: searching for East also matches EAST and east. It applies one criterion to one range. For conditions that must be met together, such as a region and a minimum sales amount, COUNTIFS is usually the better choice.

Basic Examples with Text and Numbers

Suppose A2:A8 contains these regions, in order: East, West, East, North, West, East, South. B2:B8 contains the corresponding sales amounts: 120, 85, 200, 120, 95, 160, 70. These examples show the difference between finding a specific value and applying a comparison:

With the regions in A2:A8, use =COUNTIF(A2:A8,"East"). The result is 3, because East appears three times.
With the sales amounts in B2:B8, use =COUNTIF(B2:B8,">100"). The result is 4: 120, 200, 120, and 160 are each greater than 100.
With the sales amounts still in B2:B8, use =COUNTIF(B2:B8,120). The result is 2, because two cells equal 120.
With B2:B8 as the range, use =COUNTIF(B2:B8,"<=100"). The result is 3: 85, 95, and 70 are no more than 100.

The boundary in a comparison matters. “Greater than 100” excludes 100, while “greater than or equal to 100” includes it. If you are checking a threshold, choose the operator that matches the rule you intend to apply.

Text Searches and Wildcards

For text criteria, COUNTIF can match an entire cell’s value or use wildcard characters to match part of a value. The asterisk (*) represents any number of characters, including none. The question mark (?) represents exactly one character. These are useful when spelling, suffixes, or other parts of the text vary.

For example, suppose A2:A8 contains Ada Lovelace, Alan Turing, Grace Hopper, Ada King, Katherine Johnson, Alan Kay, and Linus Torvalds. With those names in A2:A8:

=COUNTIF(A2:A8,"A*") returns 4. It counts names that begin with A: Ada Lovelace, Alan Turing, Ada King, and Alan Kay.
=COUNTIF(A2:A8,"*son") returns 1. The asterisk allows any characters before son, so Katherine Johnson matches.
=COUNTIF(A2:A8,"????") returns 0. It counts cells containing exactly four characters, and none of these full names does.

If a cell contains a literal asterisk or question mark that you want to find, precede that wildcard with a tilde (~). For instance, the criterion "~*" means a literal asterisk rather than “any text.” A tilde can itself be treated literally by using two tildes in the criterion.

Using a Cell Reference as the Criterion

A criterion does not have to be typed into the formula. Referencing a cell makes a count easier to update: change the value in the criterion cell and the result recalculates. This is helpful for a small report where someone may want to count several regions or test different thresholds.

With region names in A2:A8 and the word East in F2, use =COUNTIF(A2:A8,F2). It returns 3. Because F2 supplies the criterion, replacing East with West in F2 changes the count to 2 without editing the formula.

For a comparison that uses a cell reference, join the operator to the referenced value with the ampersand (&). With sales amounts in B2:B8 and 100 in F2, use =COUNTIF(B2:B8,">"&F2). The result is 4. Writing ">"&F2 builds the criterion “greater than 100”; simply putting ">F2" in quotes would search for text containing those characters instead.

Counting Dates and Blank Cells

Excel stores valid dates as numeric values, which allows COUNTIF to compare them. A date criterion is often clearest when built with DATE(year, month, day), rather than typed as text whose interpretation could depend on regional date settings.

Suppose D2:D8 contains actual Excel dates in this order: December 31, 2025; January 1, 2026; February 14, 2026; June 30, 2026; December 31, 2026; January 1, 2027; and June 15, 2025. With those dates in D2:D8, use =COUNTIF(D2:D8,">="&DATE(2026,1,1)). The result is 5, because five dates are on or after January 1, 2026.

For blank cells, COUNTIF can use an empty-string criterion. Suppose E2:E8 contains Paid, a truly blank cell, Pending, another blank, Paid, a formula that displays an empty string, and Pending. With those values in E2:E8, =COUNTIF(E2:E8,"") returns 3: it counts the two empty cells and the cell whose formula displays no text. This can be useful, but it also means a count of “blank-looking” cells may include formulas that are present but return an empty string.

If a date count unexpectedly returns zero, check that the cells contain real Excel dates rather than text that merely looks like dates. A number format changes how a value appears; it does not convert text into a date value.

COUNTIF Compared with COUNTIFS

COUNTIF is the right fit when there is one test. If you need several criteria to be true at the same time, use COUNTIFS, which accepts pairs of criteria ranges and criteria. For an order to qualify as both East and greater than 100, the region and sales amount must match on the same row.

With regions in A2:A8 and the corresponding sales amounts in B2:B8, use =COUNTIFS(A2:A8,"East",B2:B8,">100"). The result is 3: the East rows have sales of 120, 200, and 160, all of which exceed 100. Each criteria range should cover the corresponding rows in the data; using ranges of different sizes can lead to an error or an unintended result.

COUNTIFS treats its criteria as conditions that must all be satisfied (an AND test). If the intended logic is “East or West,” one option is to add two COUNTIF results. With the regions in A2:A8, =COUNTIF(A2:A8,"East")+COUNTIF(A2:A8,"West") returns 5 for the example data. This addition works because a row cannot be both of those different region names at once.

Common Problems and How to Fix Them

A COUNTIF result that seems wrong often comes from a mismatch between the criterion and the data, rather than a problem with the function. Check the following before rebuilding a formula:

Text versus numbers: A cell that contains the text “120” may not behave like a cell containing the numeric value 120. Check how the data was imported or entered, and convert text values if a numeric comparison is needed.
Extra spaces: A value such as East followed by a space may not match the criterion East. TRIM can help clean imported text, but COUNTIF does not remove stray spaces automatically.
Wrong comparison construction: For a threshold held in another cell, join the operator and reference, as in ">"&F2. Keep the operator inside quotation marks and the cell reference outside them.
Wildcards used unintentionally: An asterisk or question mark in a criterion has special meaning. Use a tilde before it when you mean to search for the actual symbol.
Range does not cover the full list: If new records are added below a fixed range, the formula will not include them. Consider using an Excel Table and structured references for expanding data, or update the range deliberately.
Dates stored as text: A date-looking entry may not be comparable as a date. Confirm that Excel recognizes it as a date value, and use DATE to make the criterion unambiguous.

COUNTIF is case-insensitive, so it is not suitable when uppercase and lowercase versions must be counted separately. Also remember that it returns a count, not a list of matching records. If you need to inspect or extract matching rows, filtering the data or using a suitable dynamic-array function may be more useful.

Frequently Asked Questions

What is the difference between COUNT and COUNTIF?
COUNT counts cells containing numbers. COUNTIF counts cells that meet a condition, which can be a text match, a numeric comparison, or another supported criterion. Use COUNT when you simply need a numeric-entry count and COUNTIF when you need to test a rule.

Can COUNTIF use more than one condition?
No. COUNTIF takes one criterion. Use COUNTIFS for multiple conditions that must be satisfied together, or combine separate COUNTIF formulas when your logic calls for separate counts.

Does COUNTIF distinguish uppercase from lowercase?
No. COUNTIF is not case-sensitive, so East and EAST match the same criterion. If your task depends on letter case, use a case-sensitive approach rather than relying on COUNTIF alone.

How do I count cells that are not equal to a value?
Use the not-equal operator, written as <>. With regions in A2:A8, =COUNTIF(A2:A8,"<>East") counts cells whose value is not East. Be aware that blank cells may also be included by a not-equal criterion, so check whether blanks should be part of your result.

Why does COUNTIF return zero when the text looks identical?
Common causes include extra spaces, different underlying values such as text numbers versus numeric values, or a range that does not include the cell you expect. COUNTIF ignores case, but it does not automatically clean or normalize the contents of cells.

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.