Countifs with arrayformula in Google Sheets

COUNTIFS answers a question every analyst needs to answer constantly: how many rows match these conditions? This tutorial covers the full range of COUNTIFS patterns in Google Sheets — single criterion, multiple criteria with COUNTIFS, dates, wildcards, OR logic via addition, and the gotchas that catch people the first time.

Single criterion baseline

=COUNTIFS(A:A, "Active")

Start with the simple case: count cells in column A whose value is exactly "Active". COUNTIFS is case-insensitive for plain text comparisons, so "active" and "ACTIVE" both count. Wrap the criterion in quotes if it is text, write a number bare, or pass a comparison like ">100" in quotes (the operator is part of the string).

Multiple criteria with COUNTIFS

=COUNTIFS(A:A, "Active", B:B, ">=2025-01-01", C:C, "Web")

COUNTIFS extends COUNTIFS to as many range/criterion pairs as you need. Every range must be exactly the same size — full columns work, but they must all be full columns. Conditions are AND-ed: a row counts only if it satisfies every pair. There is no built-in OR; chain together two COUNTIFS with addition for that case (see below).

OR logic with addition

Sheets has no COUNTIFS_OR. To count rows where status is "Active" OR "Trial", add two COUNTIFS calls: =COUNTIFS(A:A, "Active", B:B, "Web") + COUNTIFS(A:A, "Trial", B:B, "Web"). For longer OR lists, switch to SUMPRODUCT or to COUNTA(FILTER(...)) with a REGEXMATCH condition. Both stay readable up to maybe four alternatives; beyond that, build a helper column or move to QUERY.

Date range counts

Counting rows in a date range is the most-asked variant: =COUNTIFS(B:B, ">="&DATE(2025,1,1), B:B, "<"&DATE(2025,2,1)). Notice the operator and the date are concatenated with &; a literal string like ">=2025-01-01" works too but is more fragile because Sheets has to parse the date. Always use the bound pattern (>= start, < next-period start) — it handles month boundaries cleanly and avoids off-by-one bugs at midnight.

Wildcards

Use * for any string and ? for any single character. =COUNTIF(A:A, "order-*") counts every cell starting with "order-". To match a literal asterisk or question mark, prefix it with a tilde: "~?". Wildcards only work on text comparisons; combining them with numeric operators produces unexpected results.

Worked examples

Active customers signed up in January 2025

=COUNTIFS(Status:Status, "Active", Signup:Signup, ">="&DATE(2025,1,1), Signup:Signup, "<"&DATE(2025,2,1))

Lock dates with DATE() so locale-specific date strings don't break the formula.

Tickets in Open or In Progress

=COUNTIF(Status:Status, "Open") + COUNTIF(Status:Status, "In Progress")

Or use SUM(COUNTIF(Status:Status, {"Open","In Progress"})) for a tighter expression.

Rows where Notes mentions a refund

=COUNTIF(Notes:Notes, "*refund*")

Wildcards match anywhere in the cell text.

Common mistakes (and how to avoid them)

Different-sized ranges in COUNTIFS — Sheets returns #VALUE!. Use whole columns or matching named ranges.
Quoting numbers when they should be bare: COUNTIFS(A:A, "5") only matches the text "5", not the number 5. Drop the quotes for numeric exact matches.
Forgetting that the operator goes inside the criterion string: COUNTIFS(A:A, >5) is a syntax error; COUNTIFS(A:A, ">5") is correct.
Treating empty cells as zero. Use COUNTIFS(A:A, "") for blanks specifically and COUNTIFS(A:A, "<>"&"") for non-blanks.

Tips that pay off later

For OR with several alternatives: =SUMPRODUCT(--(A2:A100={"Open";"Pending";"Hold"})) reads as a single line.
Bound your ranges to the data instead of whole columns when sheets get large; performance drops sharply on full-column COUNTIFS in 100k+ row sheets.
When the criteria live in cells, concatenate: COUNTIFS(A:A, ">="&D2) uses the threshold from D2 without hardcoding.

When to reach for something else

If you need both counts and other aggregates over the same conditions, do a single QUERY(... GROUP BY ...) instead of three separate COUNTIFS — it scans the data once.