What is the difference between COUNTIF and COUNTIFS?

COUNTIF takes one condition; COUNTIFS takes multiple pairs of range and criteria.

Is COUNTIF case-sensitive?

No. "apple", "Apple" and "APPLE" all match. For case-sensitive counts use SUMPRODUCT(--EXACT(range,"Apple")).

How do I count cells greater than a number?

Use a quoted operator criteria: COUNTIF(B2:B5,">150"). Concatenate a cell as ">"&E2.

Do wildcards work in COUNTIF?

Yes. * matches any run of characters, ? matches exactly one. Escape a literal * or ? with ~.

How do I count blank cells?

Use COUNTBLANK(range). COUNTIF(range,"") matches empty strings and blanks, which is not always what you want.

Why are numbers stored as text not counted?

Numeric comparison criteria ignore text-formatted numbers; convert the column to real numbers first.

COUNTIF Function in Excel — Syntax, Live Playground & 8 Real Errors Fixed

// 02 — Conditional count

What COUNTIF actually matches

COUNTIF counts cells that meet one criteria. It is case-insensitive and supports wildcards — two facts that surprise people. Every row was measured live in HyperFormula on the sample table. Click any row to load it.

Measured criteria gallery

Expression	Result	Why
=COUNTIF(A2:A5,"Apple")	2	Count cells equal to Apple
=COUNTIF(A2:A5,"an")	1	Wildcard an matches Banana only
=COUNTIF(B2:B5,">150")	2	Count amounts > 150 (200, 300)
=COUNTIF(B2:B5,">=150")	3	Inclusive: 150, 200, 300
=COUNTIF(D2:D5,"East")	2	Count the East region
=COUNTIF(A2:A5,"<>Apple")	2	Not-equal: Banana and Cherry
=COUNTIF(A2:A5,"?pple")	2	? matches one character (Apple)
=COUNTIF(A2:A5,"apple")	2	Case-insensitive: apple = Apple

Two surprises, measured

Expression	Result	Why
=COUNTIF(A2:A5,"apple")	2	Case-insensitive: lowercase apple still matches Apple
=COUNTIF(A2:A5,"Apple")	2	Same count — case does not matter
=COUNTIF(A2:A5,"*")	4	Wildcard * counts every non-empty text cell
=COUNTIF(B2:B5,">150")	2	Numeric comparison criteria

⚠ COUNTIF is case-insensitive. "apple", "Apple" and "APPLE" all match. For a case-sensitive count you need SUMPRODUCT with EXACT.

Wildcards: * = any run of characters, ? = exactly one character. Escape a literal wildcard with ~ (e.g. ~*).

// 03 — Try it live

Interactive COUNTIF playground

Edit the table or type any formula — the engine evaluates instantly. Load a preset to see COUNTIF match text, operators and wildcards, and prove case-insensitivity.

A1Sample data — click any cell to edit, the formula re-evaluates live

Formula live

Examples — click to load

Grid theme

Result

—

✓ readyHyperFormula 3.5

// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

🔍

// Go deeper

Master conditional math

COUNTIF is one piece. The full course walks the toolkit end to end with live, measured examples.

COUNTIF / SUMIF — one condition
COUNTIFS / SUMIFS — many conditions
Wildcards * and ? and the ~ escape
Case-insensitivity and the EXACT fix

Free while in beta

Browse the function library

No account needed. Every page has a live playground.

The COUNTIF function,
measured & explained.

How COUNTIF works

What COUNTIF actually matches

Measured criteria gallery

Two surprises, measured

Interactive COUNTIF playground

The real-world error library

Where COUNTIF works

Master conditional math

Frequently asked

How COUNTIF works

What COUNTIF actually matches

Measured criteria gallery

Two surprises, measured

Interactive COUNTIF playground

The real-world error library

Where COUNTIF works

Master conditional math

Frequently asked

Related functions