COUNTIF in Excel: Count Cells That Equal a Value

With values in A2:A10 and the value Apple to match, use =COUNTIF(A2:A10,"Apple") to count cells equal to Apple; if three cells contain that value, the result is 3. COUNTIF treats the criterion as an equality test by default, so you do not need to add an equals sign before the value.

Basic syntax and exact equality

COUNTIF counts cells in a range that meet one condition. Its syntax is =COUNTIF(range, criteria): the first argument is the cells to inspect, and the second describes what must be true for a cell to count. For an ordinary text or number criterion, COUNTIF checks whether the cell’s value matches that criterion.

For instance, with product names in A2:A10 and three cells containing Apple, =COUNTIF(A2:A10,"Apple") returns 3. The quotes mark Apple as text in the formula. If the range contains Apple, Pear, Apple, Orange, and Apple, those three Apple entries count; other values do not. Matching is not case-sensitive, so Apple and APPLE are treated as the same text for this function.

“Equal” here means that the cell matches the criterion, not that the cell’s formula or formatting is identical. Two cells can display the same result while using different formulas and still both count. Conversely, a value that only looks similar may not match: a text value with an extra space, for example, can differ from the intended text.

Use a cell as the value to match

You can put the value you want to count in a worksheet cell and refer to it as the criterion. With product names in A2:A10 and Apple entered in D1, use =COUNTIF(A2:A10,D1). If three cells in the range match D1, the result is 3. This approach is useful when a report needs to be updated for different search terms: change D1 instead of editing the formula.

The criterion reference can be relative or absolute, depending on how you plan to copy the formula. With products in A2:A10 and the search term in D1, use =COUNTIF(A2:A10,$D$1) if you will copy the formula and want D1 to stay fixed. The dollar signs lock both the column and row. In contrast, A2:A10 will shift if you copy the formula down; use =COUNTIF($A$2:$A$10,$D$1) when the data range and search cell should both remain fixed.

Remember that a cell-based criterion can also contain wildcard characters. If D1 contains an asterisk or question mark, COUNTIF may interpret it as a wildcard rather than as an ordinary character. The wildcard section below explains how to handle that case.

Counting numbers, dates, and blanks

For a numeric equality test, enter the number as the criterion. With scores in B2:B20 and a score of 100 to count, =COUNTIF(B2:B20,100) returns the number of cells whose value equals 100. You can also keep the target number in a cell. With scores in B2:B20 and the target in E1, =COUNTIF(B2:B20,E1) counts values equal to E1.

Excel stores dates as serial numbers and displays them using date formatting. For straightforward date matching, use a cell that contains a real Excel date as the criterion. With dates in C2:C31 and the target date in E1, =COUNTIF(C2:C31,E1) counts entries equal to that date. If a date was imported as text, it may not behave like a genuine date value; check the cell’s underlying value and convert inconsistent data before relying on the count.

To count empty cells, use an empty-string criterion. With entries in A2:A20, =COUNTIF(A2:A20,"") counts cells Excel treats as blank, including cells containing formulas that return an empty string. That distinction can matter in a report: a formula-generated empty display may look blank but is not an actually unused cell. To count nonblank cells instead, use =COUNTIF(A2:A20,"<>"). Check your range carefully if the result is meant to represent cells that have been manually filled in, because a formula returning an empty string can affect how blank and nonblank tests are interpreted.

Text matching, case, and wildcards

COUNTIF is case-insensitive. With names in A2:A20, =COUNTIF(A2:A20,"sam") counts matching entries whether they appear as Sam, SAM, or sam. COUNTIF does not provide a switch for case-sensitive matching. If capitalization must distinguish two values, use a different approach, such as a formula built around the EXACT function, rather than assuming COUNTIF will separate them.

COUNTIF also supports wildcards in text criteria. An asterisk stands for any number of characters, while a question mark stands for one character. With email addresses in A2:A20, =COUNTIF(A2:A20,"*@example.com") counts entries ending in @example.com. With codes in A2:A20, =COUNTIF(A2:A20,"AB?7") matches a four-character pattern with any one character in the third position.

If you need to count a literal asterisk or question mark, place a tilde before the wildcard character. With symbols in A2:A20, =COUNTIF(A2:A20,"~*") counts cells containing a literal asterisk. The same escaping principle applies to a question mark, and a tilde itself is represented by two tildes in the criterion. This is a common source of surprising counts when data includes wildcard characters as ordinary text.

When the condition is greater than or less than

COUNTIF can test comparisons as well as equality. Put the comparison operator and value together inside quotation marks. With test scores in B2:B20, =COUNTIF(B2:B20,">=70") counts scores that are at least 70. The operators include greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>).

When the comparison value is stored in a cell, join the operator text to that reference with an ampersand. With scores in B2:B20 and a threshold in E1, =COUNTIF(B2:B20,">="&E1) counts scores greater than or equal to the value in E1. This lets a user adjust the threshold without changing the formula. For a date threshold, the same construction works when the cells contain real Excel dates. For example, with transaction dates in C2:C31 and the cutoff date in E1, =COUNTIF(C2:C31,">="&E1) counts dates on or after that cutoff.

A frequent misconception is that an equality criterion must be written as a quoted equals sign followed by the value. For ordinary equality, it does not: =COUNTIF(A2:A10,"Apple") already means equal to Apple. The operators are needed when the test is not simple equality, such as greater than a threshold or not equal to a particular entry.

Common problems and how to troubleshoot them

Unexpected spaces: A value such as Apple followed by a space may fail to match the clean criterion Apple. Inspect the source data, or clean it in a helper column if spaces or imported characters are widespread.
Numbers and text are mixed: A cell can contain a number stored as text rather than a numeric value. Imported data may also mix formats. Check representative cells and normalize the column before interpreting a count as a definitive total.
The range moved: When copying a formula, a relative range changes position. Use dollar signs to lock the range when that is the intended behavior, and verify the formula in the copied cells.
Wildcards are being interpreted: A criterion containing * or ? may match a pattern rather than the literal character. Escape a literal wildcard with a tilde.
The result is zero: Confirm that the criterion is in the expected cell, that the selected range covers the data, and that the criterion has the right value and data type. Look for leading or trailing spaces and for cells that only appear to contain numbers or dates.

For a quick check, select a few cells that should match and inspect their contents in the formula bar. Formatting can make distinct underlying values look alike: for example, a number formatted with no decimal places may display like a nearby value even though the stored numbers are different. COUNTIF evaluates the cell value, not just the visible formatting.

COUNTIF compared with related functions

COUNTIF is the simplest choice when one condition applies to one range. If the task requires several conditions at once, use COUNTIFS. For example, a sales table might need to count rows for one region and one product category together. COUNTIFS accepts a range and criterion for each condition, while COUNTIF handles only one criterion. Keep the ranges aligned so each condition is evaluated against corresponding rows.

If you need to count matches with a case-sensitive rule, COUNTIF is not sufficient because its text matching ignores case. If you need to count cells based on a more involved calculation or a condition that cannot be expressed as a standard COUNTIF criterion, a different formula approach may be more suitable. The best choice depends on whether the requirement is one simple equality test, multiple filters, case sensitivity, or custom logic.

For a simple exact match, a helper column or a complex formula is usually unnecessary. Start with COUNTIF, confirm the range and criterion, and add complexity only when the counting rule actually calls for it. In Microsoft 365 on Windows, the same basic COUNTIF syntax also works in Excel for Mac and Excel for the web; interface details can differ, but the formula itself does not.

Frequently Asked Questions

Does COUNTIF need an equals sign in the criteria?
No. With values in A2:A10, =COUNTIF(A2:A10,"Apple") counts cells equal to Apple. Add an operator only when you need a comparison such as greater than, less than, or not equal to.

Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase text as equivalent. If capitalization must distinguish matches, use a case-sensitive alternative rather than COUNTIF alone.

How do I count cells equal to another cell?
Put the target value in a worksheet cell and use that reference as the second argument. With the values in A2:A10 and the target in D1, =COUNTIF(A2:A10,D1) counts cells matching D1.

Can COUNTIF count blank cells?
Yes. With the cells to inspect in A2:A20, =COUNTIF(A2:A20,"") counts cells Excel treats as blank. Be aware that formulas returning an empty string can also be included in this result.

Why does COUNTIF return zero when the value looks the same?
Check for hidden spaces, text-versus-number differences, an incorrect range, or a criterion that Excel interprets as a wildcard. Also verify the underlying value rather than relying only on the way the cell is formatted.

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.