COUNTIF

Count the cells in a range that meet a single condition with Excel's COUNTIF function.

Statistical
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=COUNTIF(range, criteria) Returns: Number

Arguments

Argument Required Description
range Yes The range of cells you want to count.
criteria Yes The condition that decides which cells get counted. Can be a number, text, a cell reference, or an expression like ">=100" or "North*".

About

COUNTIF counts how many cells in a range match one condition. You give it a range to check and a criterion to match, and it returns the number of cells that pass. Use it when you need a quick tally, like how many orders were shipped, how many scores hit a passing grade, or how many times a name appears in a list.

The criterion can be a number, text, or a comparison like ">=80" or "<>0". You can also use wildcards in text criteria: ? matches any single character and * matches any run of characters. COUNTIF is case-insensitive, so "north" and "NORTH" count the same.

COUNTIF only handles one condition. When you need to count against two or more conditions at once, use COUNTIFS. To add up values that meet a condition instead of counting them, reach for SUMIF, and for a conditional average try AVERAGEIF.

Examples

Count against a target in a cell

See how to compare against a value stored in a cell. Join the operator and the cell reference with &, then change the target in E1 and watch the count update.

Spreadsheet editor

Count text with a wildcard

The * wildcard matches any text after "North", so this counts Northeast, North, and Northern Sales together. Try editing a region to see what still matches.

Spreadsheet editor

Flag duplicate emails

Watch how COUNTIF counts how often each email appears when you lock the range with $. Any result above 1 marks a duplicate, so edit an address and see the counts react.

Spreadsheet editor

Watch out for

⚠

Operators need to be inside quotes

Writing the criterion as =COUNTIF(B2:B50, >=80) returns an error because Excel does not expect a bare operator there.

→ Wrap the whole condition in quotes: =COUNTIF(B2:B50, ">=80").

⚠

Comparing against a cell value

Putting the cell reference inside quotes, like ">=E1", makes Excel look for the literal text "E1" instead of the number in E1.

→ Join the operator and the reference with &: =COUNTIF(B2:B50, ">="&E1).

⚠

Expecting case-sensitive counts

COUNTIF treats "Apple" and "apple" as the same value, so you cannot count one casing separately.

→ For case-sensitive counts, combine SUMPRODUCT with EXACT instead.

⚠

Trying to use two conditions

COUNTIF only accepts one criterion, so you cannot count cells that match two rules at once.

→ Switch to COUNTIFS, which lets you stack several range and criteria pairs.

Tips & notes

Text criteria can use wildcards: ? matches any single character and * matches any number of characters. To match a literal ? or *, put a tilde (~) in front of it. Criteria strings longer than 255 characters cause COUNTIF to return errors.

Common questions

Can I use wildcards in COUNTIF?

Yes. Use ? to match any single character and * to match any sequence of characters. For example, "app*" counts cells like apple, application, and approach.

Is COUNTIF case-sensitive?

No. COUNTIF ignores letter case, so uppercase and lowercase versions of the same text are counted together.

How do I count with more than one condition?

Use COUNTIFS instead. It works just like COUNTIF but lets you add multiple range and criteria pairs in a single formula.

How do I count blank or non-blank cells?

Use "" for blanks, as in =COUNTIF(A2:A100, ""), or "<>" to count cells that contain anything. COUNTBLANK is another option for empty cells.