Jun 29, 2026

How to Use COUNTIF in Google Sheets (2026 Guide)

Google Sheets Productivity How-to Spreadsheets Formulas

COUNTIF counts how many cells in a range meet one condition. The syntax is =COUNTIF(range, criterion). Need more than one condition? Use COUNTIFS. Below are working examples for text, numbers, wildcards, dates, and multiple criteria that you can paste straight into your sheet.

1. COUNTIF Syntax

=COUNTIF(range, criterion)

range: the cells you want to check, like A2:A100.
criterion: the condition each cell must meet, like "Paid" or ">100".

The simplest example counts how many cells exactly match a value:

=COUNTIF(A2:A100, "Paid")

This returns the number of cells in A2:A100 that contain the text “Paid”. COUNTIF is not case-sensitive, so “paid”, “Paid”, and “PAID” all count.

2. Count Text Values

Match a whole word or phrase by putting it in quotes:

=COUNTIF(C2:C100, "Complete")

To count cells equal to the text in another cell, point the criterion at that cell (no quotes around the reference):

=COUNTIF(C2:C100, E1)

To count cells that are NOT a value, use the <> operator:

=COUNTIF(C2:C100, "<>Complete")

To count non-empty cells, use:

=COUNTIF(C2:C100, "<>")

3. Count Numbers and Comparisons

For numeric conditions, wrap the operator and value in quotes:

=COUNTIF(B2:B100, ">100")     greater than 100
=COUNTIF(B2:B100, ">=100")    100 or more
=COUNTIF(B2:B100, "<50")      less than 50
=COUNTIF(B2:B100, "<>0")      not equal to zero
=COUNTIF(B2:B100, "100")      exactly 100

To compare against a value in another cell, join the operator and the reference with &:

=COUNTIF(B2:B100, ">"&E1)

This counts cells greater than whatever number sits in E1, which is handy for a threshold you change often.

4. Use Wildcards for Partial Matches

Two wildcard characters work inside text criteria:

* matches any number of characters.
? matches exactly one character.

=COUNTIF(A2:A100, "*north*")   contains "north" anywhere
=COUNTIF(A2:A100, "north*")    starts with "north"
=COUNTIF(A2:A100, "*north")    ends with "north"
=COUNTIF(A2:A100, "j??n")      4 letters like Jaan, John, Joan

To search for a literal asterisk or question mark, put a tilde in front of it, like "~*".

5. Count by Date

Dates are stored as numbers, so the comparison operators work directly:

=COUNTIF(D2:D100, ">1/1/2026")          dates after Jan 1, 2026
=COUNTIF(D2:D100, "<="&TODAY())          dates up to today
=COUNTIF(D2:D100, DATE(2026,6,29))       a specific date

To count dates within a range (between two dates), use COUNTIFS, covered next:

=COUNTIFS(D2:D100, ">="&DATE(2026,1,1), D2:D100, "<="&DATE(2026,3,31))

That counts dates in the first quarter of 2026.

6. Multiple Criteria with COUNTIFS

COUNTIFS extends COUNTIF to several conditions, all of which must be true (AND logic):

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

Count rows that are both “Paid” AND over 100:

=COUNTIFS(A2:A100, "Paid", B2:B100, ">100")

Count orders in the East region under $500:

=COUNTIFS(C2:C100, "East", B2:B100, "<500")

All ranges in a COUNTIFS must be the same size and shape, or you will get a #VALUE! error.

Common Errors

Returns 0 unexpectedly: the criterion text does not exactly match, check for trailing spaces with =COUNTIF(A2:A100, "Paid") versus "Paid ".
#VALUE! in COUNTIFS: the ranges are different sizes. Make them match.
Operator ignored: you forgot the quotes. >100 must be written ">100".
Cell reference not working in criterion: use & to join, as in ">"&E1, not ">E1".

Quick Reference

Goal	Formula
Exact text	`=COUNTIF(A:A, "Paid")`
Greater than	`=COUNTIF(B:B, ">100")`
Contains word	`=COUNTIF(A:A, "north")`
Not equal	`=COUNTIF(A:A, "<>Done")`
Two conditions	`=COUNTIFS(A:A, "Paid", B:B, ">100")`
Date range	`=COUNTIFS(D:D, ">="&DATE(2026,1,1), D:D, "<="&DATE(2026,3,31))`

Let Carly Run the Counts You Repeat

COUNTIF answers “how many” in one cell, but if you rebuild the same tally every week, that is recurring work Carly can take. Carly is an AI assistant that connects to 200+ apps including Google Sheets and handles the repetitive follow-through, logging records into the sheet your COUNTIF reads, pulling the resulting counts into a summary email, and sending that recap on a schedule. Carly starts at $35/month.

More on Google Sheets: How to use VLOOKUP in Google Sheets · How to use the IF function in Google Sheets · How to use the QUERY function in Google Sheets · Google Sheets integration

Ready to automate your busywork?

Carly schedules, researches, and briefs you—so you can focus on what matters.

See what people say

"Before Carly, I relied on a Calendly link, but the whole process felt impersonal and not very professional. Carly changed that by handling all the back-and-forth, so I'm no longer stuck in endless email threads trying to line up schedules.

Now Carly reaches out to candidates, shares my real-time availability, lets them pick a slot, then sends a Zoom link and drops it straight into my calendar. She sends reminders to both of us before each call, which has significantly reduced no-shows and last-minute confusion.

On top of scheduling, Carly acts like a full executive assistant, sending me my schedule the night before so I can prepare for each call. It reminds me of the old x.ai assistant, but Carly is noticeably smarter, faster, and better suited to my healthcare recruitment business."

Gus Ibrahim, Founder & Director, IHR