COUNTIF and COUNTIFS: not blank, multiple criteria, not equal, dates, wildcards

COUNTIF is one function with a small language inside it: the criteria string. Most of what goes wrong — blanks that count, dates that don't, a '<>' that behaves differently in Google Sheets — is that language. Here it is, with the forms we use in our own workbooks and the ones we stopped using.

Contents

The shape

one condition, several conditions

=COUNTIF(range, criteria)
=COUNTIFS(range1, criteria1, range2, criteria2, …)     all must be true; ranges same size

The criteria is either a value ("Done", 7, a cell reference) or a string with an operator in front: ">=5", "<>Done", "<"&TODAY(). Text matches are case-insensitive. Use COUNTIFS even for one condition if you might add another — the argument order is different and changing later is a common source of bugs.

Counting what’s filled in

four ways that aren't the same

=COUNTA(F8:F157)          anything non-empty, including formulas that return ""
=COUNT(F8:F157)           numbers only
=COUNTIF(F8:F157,"<>")    non-empty — but see below
=COUNTBLANK(F8:F157)      empty cells and cells whose formula returns ""

For an input column where people type a tick or leave the cell empty, COUNTA is the right one and it’s what our habit and attendance grids use: any mark counts, empty doesn’t. Don’t type 0 to mean “no” — it’s a mark.

For a column of formulas that return "" when there’s nothing to show, COUNTA counts the formulas. COUNTIF(range,"<>") is what most guides suggest, but a cell holding "" from a formula counts as non-blank in Excel and as blank in Google Sheets, so the number changes with the app. The portable count of non-empty formula results is arithmetic:

count non-empty results of formulas

=SUMPRODUCT(LEN(B6:AF6)>0)   → in Excel: =SUMPRODUCT(--(LEN(B6:AF6)>0))
or, when every result is one character (a letter code):
=SUMPRODUCT(LEN(B6:AF6))

Several conditions

this person, this status

=COUNTIFS(Tasks!$I$8:$I$157, $A14, Tasks!$G$8:$G$157, "Overdue")

The ranges must be the same height; a bounded range like $8:$157 rather than a whole column keeps Google Sheets fast and makes the formula say where it ends.

The criteria language on this page is the same one SUMIFS uses — swap counting for adding and everything below still applies. The adding side, with date ranges, another sheet and the reasons it returns 0, is in the SUMIFS cheat sheet.

Not equal

"<>Done" counts everything that isn’t Done — including empty rows. That’s usually not what you meant: an empty task list would report 150 open tasks. Two fixes:

subtract instead of negate

=COUNTIF(range, person) - COUNTIFS(range, person, status, "Done")     all theirs, minus done
=COUNTIF(status, "Open") + COUNTIF(status, "Soon")                     name the states you want

Subtraction is what our checklist’s Board sheet does. It reads the same in every app and never counts the blanks.

Dates

A date typed in the criteria string is parsed by locale — ">1/9/2026" is January in one country and September in another. Build the date and concatenate the operator:

in this month

=COUNTIFS(dates, ">="&DATE(2026,9,1), dates, "<="&EOMONTH(DATE(2026,9,1),0))
=COUNTIFS(dates, ">"&$B$3-7, dates, "<="&$B$3)      last seven days up to the cell B3

Same for numbers in cells: ">="&E4, never ">=E4" (that’s the four-character text E4).

Two bounds rather than MONTH(dates)=9, because bounds keep the year and let a range stay a range. EOMONTH and the rest of the month arithmetic these criteria are built from are in date formulas in Excel.

OR: add the counts

COUNTIFS is AND. For OR on the same column, add COUNTIFs; for OR across columns with overlap, add and subtract the intersection, or use a helper column that says which state the row is in and count that — the status column in the checklist exists partly so nothing needs an OR.

Wildcards

"*" any characters, "?" exactly one, "~*" a literal asterisk. "Har*" counts everything beginning with Har. Wildcards only match text: COUNTIF(range,"?*") counts text cells and skips numbers — we used it once to count “filled” days in a grid, and it missed every day someone had typed a 1. COUNTA was right.

Running count: numbering duplicates

The one COUNTIF pattern worth memorising:

expanding range

D5: =COUNTIF($A$5:A5, A5)      1 for the first time this value appears, 2 for the second…
fill down

The first reference is anchored, the second moves. This numbers events on the same date in our calendar, overdue tasks in the checklist, and duplicates anywhere you need to pick “the second one” with a plain lookup instead of an array formula.

Distinct values

count distinct, blanks excluded

=SUMPRODUCT((A5:A200<>"")/COUNTIF(A5:A200, A5:A200&""))

Each value contributes 1 divided by how many times it appears; the &"" stops COUNTIF from treating blanks as zeros. Slow on thousands of rows; fine on hundreds.

Numbers stored as text

COUNTIF(range, 7) won’t count a cell containing the text "7", and COUNTIF(range, ">5") ignores text numbers entirely. If a count is lower than the eye says, select the column and look for the green triangle or left-aligned numbers. Convert with VALUE() or by multiplying by 1.

What we do

COUNTA for input marks, COUNTIFS with "Done"-style positive criteria for statuses, subtraction instead of "<>", DATE() concatenated for date bounds, bounded ranges always, SUMPRODUCT(LEN()) when counting formula results. Every count in a Pro workbook is written out on its Formulas sheet in one of these forms.