COUNTIF counts how many cells in a range meet a condition. It's one of those functions you end up using constantly once you know it exists — counting how many tasks are "Done," how many orders are over $100, how many names appear more than once.

---

The Syntax

Code
=COUNTIF(range, criterion)
  • range — the cells to check
  • criterion — the condition that must be true for a cell to be counted

---

Basic Examples

Count how many cells in A2:A100 contain the word "Done":

Code
=COUNTIF(A2:A100, "Done")

Count how many cells in B2:B100 are greater than 100:

Code
=COUNTIF(B2:B100, ">100")

Count how many cells contain a specific value from another cell (say, C1):

Code
=COUNTIF(A2:A100, C1)

Count how many cells are not blank:

Code
=COUNTIF(A2:A100, "<>")

Count blanks:

Code
=COUNTIF(A2:A100, "")

---

Wildcard Matching

COUNTIF supports two wildcards:

| Wildcard | Meaning | |----------|---------| | * | Any sequence of characters | | ? | Any single character |

Count cells that contain "Pending" anywhere (not just exact match):

Code
=COUNTIF(A2:A100, "*Pending*")

Count cells that start with "INV":

Code
=COUNTIF(A2:A100, "INV*")

---

Multiple Conditions: COUNTIFS

COUNTIFS extends COUNTIF to support multiple conditions. All conditions must be true for a row to be counted.

Code
=COUNTIFS(range1, criterion1, range2, criterion2, ...)

Count rows where Status is "Done" AND Amount is greater than 500:

Code
=COUNTIFS(A2:A100, "Done", B2:B100, ">500")

Count rows where the date is in April 2026:

Code
=COUNTIFS(A2:A100, ">="&DATE(2026,4,1), A2:A100, "<"&DATE(2026,5,1))

The &DATE() syntax combines a comparison operator (>=) with a date value. The quotation marks go around the operator, not the full expression.

---

Common Errors

Returns 0 when you expect a count

Most common cause: the criterion text doesn't exactly match the cell content. "done" and "Done" are counted the same (COUNTIF is case-insensitive), but "Done " (with a trailing space) won't match "Done". Use =TRIM() to clean the data.

Counting numbers stored as text

If your numbers are stored as text (left-aligned in the cell), ">100" won't match them. Convert to real numbers first: select the column, go to Format > Number > Number.

COUNTIF with a date

Dates in Google Sheets are stored as numbers. COUNTIF(A2:A100, "2026-04-01") won't work because the string "2026-04-01" doesn't equal the date serial number. Use:

Code
=COUNTIF(A2:A100, DATE(2026,4,1))

Or use COUNTIFS with a range as shown above.

---

COUNTIF vs SUMIF vs AVERAGEIF

These three functions follow the same pattern:

| Function | What it does | |----------|-------------| | COUNTIF | Counts cells meeting a condition | | SUMIF | Sums a range based on a condition in another range | | AVERAGEIF | Averages a range based on a condition |

SUMIF syntax:

Code
=SUMIF(criteria_range, criterion, sum_range)

Sum all amounts where the category is "Software":

Code
=SUMIF(C2:C100, "Software", B2:B100)

All three have ...IFS versions (COUNTIFS, SUMIFS, AVERAGEIFS) for multiple conditions.

---

Practical Use Cases

Count how many tasks are complete:

Code
=COUNTIF(Status_column, "Done")

Count how many items are overdue (date in the past, status not Done):

Code
=COUNTIFS(DueDate_column, "<"&TODAY(), Status_column, "<>Done")

Count unique values (COUNTIF can't do this directly — use COUNTUNIQUE):

Code
=COUNTUNIQUE(A2:A100)

Check for duplicates (count how many times a value appears):

Code
=COUNTIF($A$2:$A$100, A2)

If this returns more than 1, the value in A2 appears multiple times.

---

What to Build on Top of This

Don't want to set this up yourself? Describe what you're counting and it'll be configured for your sheet. Get it installed