Microsoft Excel COUNTIF Formula: Syntax and Examples

Formulas & functions · Updated November 20, 2026

If product names are in A2:A100, count cells containing Apples with =COUNTIF(A2:A100,"Apples"). COUNTIF counts cells in a range that meet one condition, such as matching text, exceeding a number, or meeting a date threshold.

COUNTIF syntax and what it does

COUNTIF has two arguments: the range to examine and the criterion each cell must satisfy. Its general pattern is COUNTIF(range, criteria); when entering a formula in a worksheet, replace those labels with a real cell range and a real criterion. The function returns one number: the count of matching cells.

For instance, it can answer questions such as “How many orders came from the West region?” or “How many scores were above 80?” It does not add the values in matching rows. To total numbers based on a condition, use SUMIF instead. COUNTIF is useful for quick summaries, checks, and dashboard metrics, especially when you need one rule rather than several.

In Microsoft 365 for Windows, enter the formula in a cell and press Enter. Excel recalculates it when the referenced cells change. The formula works in current Excel for Mac and Excel for the web as well; the basic syntax is the same. If an installation uses a regional setting that expects semicolons, Excel may show semicolons in formulas instead of commas.

A simple example with a text list

Suppose A2:A8 contains these entries in order: North, South, North, East, South, North, and one empty cell. To count North, use the following formula. It returns 3 because North appears three times.

With regions in A2:A8, enter =COUNTIF(A2:A8,"North").

Text criteria are enclosed in straight double quotation marks. The match is for the cell’s content, not a case-sensitive spelling: COUNTIF treats “north” and “North” as the same criterion. A cell containing “Northwest” does not match the exact criterion “North,” because the text is not identical. Wildcards, explained below, allow partial matches.

The range can be a single column, a row, or a rectangular block of cells. For a dependable worksheet, keep the range limited to the data area or use an Excel Table column rather than selecting unrelated cells. If new records are regularly added, a Table reference can expand automatically as rows are added.

Using numbers, comparison operators, and cell references

A numeric criterion can be a number or a comparison such as greater than, less than, equal to, or not equal to. For comparisons, put the operator and value inside quotation marks. Assume C2:C7 contains 12, 18, 25, 31, a blank, and 18. The following formula counts numbers greater than 20 and returns 2.

With those values in C2:C7, enter =COUNTIF(C2:C7,">20").

Operators include >, <, >=, <=, and <> for “not equal to.” For a criterion that should change when a user edits a cell, join the operator text to a cell reference with the ampersand. If D1 contains 20, this formula also returns 2:

With values in C2:C7 and the threshold 20 in D1, enter =COUNTIF(C2:C7,">"&D1).

This construction is useful in reports where the threshold is an input rather than a fixed number embedded in the formula. The operator remains text, while Excel reads the value from D1. Without the ampersand, Excel would not combine the comparison operator and the referenced value into one criterion.

Wildcards for partial text matches

COUNTIF supports two wildcards in text criteria. An asterisk (*) stands for any number of characters, including none; a question mark (?) stands for exactly one character. These are useful when entries contain extra words, codes, or inconsistent endings.

  • Use "North*" to match text beginning with North, such as North and Northwest.
  • Use "*shop" to match text ending in shop, such as Bookshop.
  • Use "B?g" to match three-character text such as Bag or Big.

Assume A2:A6 contains North, Northwest, South, North Zone, and East. To count every entry beginning with North, use this formula; the result is 3.

With those region labels in A2:A6, enter =COUNTIF(A2:A6,"North*").

If the data contains an actual asterisk or question mark that you want to match literally, prefix the character with a tilde (~). For example, the criterion "~*" searches for a literal asterisk rather than treating it as a wildcard. This distinction matters when counting product descriptions or imported data that use these symbols as ordinary characters.

Counting dates, blanks, and nonblank cells

Excel stores dates as serial numbers, so COUNTIF can compare dates using the same comparison operators as numeric values. A reliable way to build a date criterion is to use DATE, which avoids ambiguity between regional date formats. Assume E2:E7 contains actual Excel dates: December 15, 2025; January 1, 2026; January 10, 2026; March 5, 2026; June 30, 2025; and February 2, 2026. The formula counts dates on or after January 1, 2026 and returns 4.

With those dates in E2:E7, enter =COUNTIF(E2:E7,">="&DATE(2026,1,1)).

This comparison includes the threshold date itself. If dates are stored as text rather than real Excel dates, Excel may not compare them as expected. Imported data is a common source of text dates; changing the cell format alone does not necessarily convert text into date values. Confirm that Excel recognizes the entries as dates before troubleshooting the criterion.

To count empty cells, use an empty-string criterion. To count cells that contain something, use the not-equal-to-empty criterion. For example, with the region list A2:A8 described earlier, the blank-count formula returns 1, while the nonblank-count formula returns 6.

With regions in A2:A8, enter =COUNTIF(A2:A8,"") to count blanks.

With the same range, enter =COUNTIF(A2:A8,"<>") to count nonblank entries.

Be aware that “blank” can mean different things in a worksheet. A truly empty cell is not the same as a cell containing a space. A formula that displays an empty string may also behave differently from a physically empty cell in some counting tasks. If counts appear surprising, inspect the underlying cell contents rather than relying only on how they look.

When one condition is not enough: COUNTIFS

COUNTIF accepts one criterion. If a count must satisfy two or more conditions—for example, a particular region and a minimum sales amount—use COUNTIFS. It takes pairs of ranges and criteria, and all conditions must be true for a row to be counted. Its ranges should have the same dimensions so that corresponding cells are evaluated together.

Suppose A2:A6 contains North, South, North, East, and North, while B2:B6 contains 500, 700, 900, 300, and 1,200. To count North rows with sales of at least 800, the formula returns 2.

With regions in A2:A6 and sales in B2:B6, enter =COUNTIFS(A2:A6,"North",B2:B6,">=800").

COUNTIFS is not just a version of COUNTIF with a longer criterion; the separate range-and-criterion pairs make the logic explicit. Use it when every condition must be met. If your requirement is “North or South,” that is an either/or condition; a common straightforward method is to add two COUNTIF results. For large reports or more complex logic, make the intended logic clear and verify that overlapping conditions are not counted twice.

Common COUNTIF problems and how to fix them

A result of 0 or a count that seems too high is often caused by the criterion or the source data rather than a broken function. Check these points before rebuilding the formula:

  • Extra spaces: “North” and “North ” can be different text values. Clean imported text or inspect a sample cell’s contents.
  • Numbers stored as text: A value that looks numeric may be text, which can affect comparisons. Convert the source values to real numbers when numerical comparison is intended.
  • Wrong range: Check that the first and last rows include all relevant records and exclude headers if those should not count.
  • Missing quotation marks: Text criteria and comparison expressions need quotation marks. When a comparison uses a cell reference, join the quoted operator and reference with an ampersand.
  • Wildcard used unintentionally: An asterisk or question mark in a criterion acts as a wildcard unless escaped with a tilde.
  • Different regional separators: Some Excel settings use semicolons between arguments. Follow the separator Excel expects in that installation.

COUNTIF is not case-sensitive. If the requirement is to distinguish “North” from “NORTH,” COUNTIF alone is not the right tool; a case-sensitive formula needs a different approach. Also, COUNTIF cannot directly apply one criterion to multiple separate ranges in one call. For separate ranges, combine suitable counts or arrange the data in one consistent range.

COUNTIF versus related counting methods

Choosing the right function helps avoid complicated formulas. COUNTIF is the best fit for one condition in one range. COUNTIFS is the natural choice for multiple conditions that must all be satisfied. COUNTA counts cells that are not empty without checking their values, while COUNT counts cells containing numbers. Neither is a substitute for a conditional count when you need to test a specific value.

For repeatable datasets, Excel Tables can make COUNTIF formulas easier to maintain. If a Table is named Orders and its column is Region, the structured-reference formula below counts entries labeled North. The table’s name and column must match the names in the workbook.

With an Excel Table named Orders and a Region column, enter =COUNTIF(Orders[Region],"North").

Tables are helpful when new rows are appended because their columns generally expand with the data. For a one-off calculation, an ordinary cell range is often simpler. Either way, keep the source data organized with one record per row and consistent values in each field; clean structure makes criteria easier to reason about and results easier to audit.

Frequently Asked Questions

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is case-insensitive, so a text criterion does not distinguish “North” from “NORTH.” A case-sensitive requirement needs a different formula or method.

Can COUNTIF count cells containing part of a word?
Yes. Use an asterisk wildcard around or beside the text for a partial match. For example, "*shop*" can match text with shop anywhere in the cell. Remember that the asterisk is a wildcard, not a literal character, unless escaped.

Why does COUNTIF return zero when the value appears in the range?
Look for hidden spaces, text-versus-number differences, an incorrect range, or a criterion that does not match the cell’s actual content. For dates, check that the cells contain real date values rather than text that only looks like a date.

Can I use COUNTIF with a condition in another cell?
Yes. For a comparison such as greater than a threshold, join the quoted operator to the cell reference with an ampersand. This lets the count update when the threshold cell changes.

What is the difference between COUNTIF and COUNTIFS?
COUNTIF tests one condition. COUNTIFS tests multiple condition-range pairs, counting a record only when all the specified conditions are met. Use COUNTIFS for combinations such as a region plus a sales threshold.

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.