How to Use COUNTIF in Google Sheets (With Examples)

How many leads said yes? How many orders are still open? How many posts mention a certain topic? When you keep records in a spreadsheet, COUNTIF answers questions like these in one cell instead of you counting rows by eye.

It's a short function with a few quirks that trip people up, mostly around quotation marks and wildcards. I'll follow Google's own function page so you know what each part does.

What the COUNTIF function does in Google Sheets

Google describes COUNTIF as returning a conditional count across a range. In plain English, it looks through a set of cells and counts the ones that meet a single condition you set.

The pattern is:

=COUNTIF(range, criterion)

• range is the block of cells to look through, such as A2:A20 or a whole column.
• criterion is the test each cell must pass, such as a word, a number or a comparison.

Counting cells that match a word with COUNTIF

Text criteria have to sit inside quotation marks. Google's sample puts a word in quotes in exactly this way, so a pattern for your own list might be:

=COUNTIF(B2:B20,"Done")

That counts every cell in B2:B20 that says Done. Matching isn't case sensitive, according to Google, so done and DONE both count. A cell with extra words around it, such as Done today, wouldn't match unless you use a wildcard.

Counting numbers above or below a value with COUNTIF

Numeric criteria can be a number, a numeric string, or a number with an operator. Google lists the operators as equals, greater than, greater than or equal to, less than and less than or equal to, written as =, >, >=, <, <=.

When you use an operator, wrap the whole test in quotation marks. Google's sample is COUNTIF(A1:A10,">20"), which counts the cells in A1:A10 greater than 20. Your version might look like this:

=COUNTIF(C2:C20,">=5")

Criterion you type	What it counts (as Google describes it)
"Done"	Cells matching that text, ignoring case
">=5"	Cells with a number greater than or equal to 5
"<5"	Cells with a number less than 5
The number alone	Cells equal to that number

Using wildcards in COUNTIF to count partial matches

Google says wildcards work in text criteria. A question mark matches any single character, and an asterisk matches zero or more characters. So you can count every cell that starts with a word:

=COUNTIF(A2:A20,"Order*")

To search for an actual question mark or asterisk, Google says to put a tilde in front of it. That matters if your cells hold text such as Interested? and you want that exact character.

Where to type a COUNTIF formula in Google Sheets

Click the empty cell where you want the answer to appear, type an equals sign, then the function name and its brackets. Sheets may offer suggestions as you type, which is handy if you forget the order of the parts. Press Enter and the count shows in that cell.

Keep your answer cells away from the data they count. A tidy habit is a small summary area to the right or on a separate tab, so inserting rows in the list never pushes your totals out of sight. Give each total a label in the cell beside it, so anyone opening the sheet later knows what the number means.

Counting a cell reference instead of typed text

Typing the word inside the formula is fine for a quick count, but a cell reference is easier to reuse. Put the word you're searching for in a cell, say E1, and write:

=COUNTIF(B2:B20,E1)

Now you change E1 to Done, Waiting or Lost and the count updates. It's a neat trick for a small summary table where each row counts a different status.

When COUNTIF is not enough and you need COUNTIFS

COUNTIF takes one condition only. Google's help points to COUNTIFS (or the database functions DCOUNT and DCOUNTA) when you need to apply multiple criteria, for example counting orders that are both Done and above a certain number.

Related functions Google lists on the same page include SUMIF, COUNTUNIQUE, COUNTA, COUNTBLANK and COUNT. If your question is how many different values appear, rather than how many match, COUNTUNIQUE is the one to read about. If your sheet has repeat rows to clear first, my guide to removing duplicates in Google Sheets helps.

COUNTIF ideas for a small business spreadsheet

• Count leads by stage: one COUNTIF per stage in a summary table.
• Count tasks marked Done in a project tracker, then compare to the total rows.
• Count customers from one town, using a wildcard if the address column has extra detail.
• Count blank answers with COUNTBLANK alongside COUNTIF to see what's missing.

Once you've got your counts, you may want the biggest ones at the top. My page on sorting in Google Sheets shows how to arrange a summary table.

If your COUNTIF in Google Sheets returns the wrong number

1. Check the quotation marks. Text criteria and operator tests need them.
2. Check the range. Make sure it covers every row you want and no extra headings.
3. Look for extra spaces or different spellings in the cells. Done and Done with a trailing space aren't the same text.
4. Look at the numbers. A number stored as text can behave differently to a real number, so retype one to test.
5. If you need two conditions, switch to COUNTIFS.

Menus and function help can change slightly between versions, so if something doesn't behave as described, check Google's current function page.

Frequently asked questions

What does COUNTIF do in Google Sheets?

It counts the cells in a range that meet one condition. The pattern is COUNTIF(range, criterion), and Google describes it as returning a conditional count across a range.

Do I need quotation marks in COUNTIF?

Yes for text criteria, and for numbers combined with an operator such as ">5". A cell reference doesn't need them.

Is COUNTIF case sensitive?

No. Google says matching is not case sensitive, so done and DONE are counted the same.

Can COUNTIF use more than one condition?

No, it takes a single condition. Use COUNTIFS, or the database functions DCOUNT and DCOUNTA, for multiple criteria.

How do I count cells that contain part of a word?

Use a wildcard. A question mark stands for any single character and an asterisk for zero or more, for example "Order*". Put a tilde before a real question mark or asterisk.