Conditional formula page

COUNTIF Formula

Use COUNTIF when one column decides whether a row should be counted.

Best for

Count matching rows with COUNTIF.

What it returns

With the sample data, this counts completed tasks.

Copy formulas

Excel formula

=COUNTIF(C2:C100, "Complete")

Google Sheets formula

=COUNTIF(C2:C100, "Complete")

Excel / Google Sheets difference

Excel and Google Sheets both use COUNTIF for one condition. It counts matching cells or rows, so it does not total a separate Hours column.

Task	Owner	Status	Due Date	Hours
Import leads	Maya	Complete	2026-01-06	3
Clean headers	Nico	In Progress	2026-01-08	2
Review budget	Maya	Complete	2026-01-12	4
Publish report	Iris	Blocked	2026-01-15	1

What it returns

With the sample data, this counts completed tasks.

Syntax piece	Role in the formula
C2:C100	The Status range checked for the requested condition.
"Complete"	The criterion that selects completed rows.
COUNTIF	Returns a count of matches and does not add numeric values from another column.

Count Complete tasks

=COUNTIF(C2:C100, "Complete")

Excel: Use the sample task table with two Complete values in C2:C100. Returns: 2

Criteria stored in a cell

=COUNTIF(B2:B100, F2)

Google Sheets: Enter Maya in F2; two Owner cells in B2:B100 contain Maya. Returns: 2

Task names beginning with Import

=COUNTIF(A2:A100, "Import*")

Excel: Use the sample task names in A2:A100, with one name beginning Import. Returns: 1

Issue	Likely cause	Fix
A wildcard criterion matches the wrong task names	An asterisk matches any sequence, while a literal asterisk needs a tilde escape.	Use Import* for a prefix match and ~* when the asterisk itself must be matched.
A visible Complete value is not counted	The cell contains hidden spaces or another nonprinting character; case differences are not the cause because COUNTIF ignores case.	Trim or clean the source status values before applying the criterion.
Two conditions are needed but COUNTIF cannot express them	COUNTIF accepts one range-and-criterion pair only.	Switch to COUNTIFS and add one aligned range-and-criterion pair for each condition.

Do not use COUNTIF when the result must add amounts or enforce multiple independent conditions.

Alternative	When to use it
COUNTIFS Formula	Use when the count must satisfy two or more conditions at the same time.
COUNTIFS Formula Builder	Use when criteria need to be configured interactively before copying the formula.

Is COUNTIF case-sensitive?

No. COUNTIF treats uppercase and lowercase letters as equal when comparing text.

How do COUNTIF wildcards work?

An asterisk matches any sequence of characters; prefix it with a tilde when a literal asterisk should be matched.