Maths and conditional totals

Excel COUNTIF Function: Count Rows That Match a Condition

Counts how many cells in a range meet a single condition.

COUNTIF answers 'how many?' where SUM answers 'how much?'. How many orders came from the North region, how many amounts were over £1,000, how many cells are still blank. It takes a range and a condition and returns a count of the cells that match.

It is also the fastest way to find duplicates. Point COUNTIF at a column and ask how many times the current row's value appears in it; anything greater than 1 is a repeat. That one formula, used in conditional formatting, is how most people highlight duplicate entries.

Like SUMIF, it handles one condition only. COUNTIFS takes several, and follows the same pattern of range-and-criteria pairs.

Syntax

=COUNTIF(range, criteria)

Arguments

range
Required
The cells to examine.
criteria
Required
The condition a cell must meet: a value ("North"), a comparison in quotes (">1000"), a cell reference, or a wildcard pattern ("A*").

The example data

Headers in row 1, data in A2:D6.

ABCD
1RegionRepCategoryAmount
2NorthAliceHardware1240
3SouthBrunoSoftware385
4NorthChenHardware2100
5SouthAliceSoftware940
6NorthBrunoSoftware156

Worked examples

=COUNTIF(A2:A6, "North")

Result: 3

Three rows are in the North region.

=COUNTIF(D2:D6, ">1000")

Result: 2

Comparisons go inside quotes. Two amounts are above 1000.

=COUNTIF(C2:C6, "<>Hardware")

Result: 3

<> means 'not equal to', so this counts the Software rows.

=COUNTIF($B$2:$B$6, B2)

Result: 2

The duplicate check. Alice appears twice, so this returns 2 — filled down the column, anything above 1 is a repeated value.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: COUNTIF Function

Common errors and how to fix them

Returns 0 when values are visible

Why it happens: Trailing spaces, or numbers stored as text so a numeric comparison never matches.

How to fix it: Clean with TRIM and confirm the column is genuinely numeric. Text numbers align left by default, which is the quickest visual check.

Counts more than expected

Why it happens: COUNTIF is not case sensitive and treats * and ? as wildcards, so a criteria string containing them matches more than intended.

How to fix it: Escape a literal asterisk or question mark with a tilde: "~*". For case-sensitive counting use SUMPRODUCT with EXACT.

#VALUE!

Why it happens: The range points at a workbook that is currently closed.

How to fix it: COUNTIF cannot read closed workbooks. Open the source file, or copy the data into the same one.

Tips worth knowing

  • Count blanks with =COUNTIF(range, "") and non-blanks with COUNTA — a fast way to audit an import for missing fields.
  • Highlight duplicates by putting =COUNTIF($A$2:$A$100, A2)>1 into a conditional formatting rule.
  • COUNTIF ignores the criteria's case, so "north" and "North" count the same rows.
  • To count on more than one condition, switch to COUNTIFS: =COUNTIFS(A2:A6, "North", C2:C6, "Hardware").

Frequently asked questions

How do I count cells that contain specific text anywhere in them?

Wrap the text in asterisks: =COUNTIF(A2:A100, "*urgent*") counts every cell with "urgent" somewhere inside it, rather than cells equal to it.

What is the difference between COUNT, COUNTA and COUNTIF?

COUNT counts cells containing numbers. COUNTA counts cells that are not empty, whatever they hold. COUNTIF counts cells meeting a condition you specify. If you want a plain total of filled rows, COUNTA is the one you want.

Can COUNTIF count unique values?

Not directly. The classic approach is =SUMPRODUCT(1/COUNTIF(range, range)), which counts each distinct value once. If your Excel has dynamic arrays, =COUNTA(UNIQUE(range)) is far clearer.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions