How to Use COUNTIF in Google Sheets

You have a list of orders with a city in column A and an amount in column B, and you want to know how many came from Toronto, or how many were over 100. COUNTIF answers that kind of question in one formula.

Type the basic formula

=COUNTIF(A2:A20, "Toronto")

• range is the block of cells to check, here A2:A20.
• criterion is what a cell has to match to be counted. Text goes in double quotes.

If four rows say Toronto, the cell shows 4. Matching is not case sensitive, so toronto and TORONTO are counted too.

Reference a cell instead of typing the value

=COUNTIF(A2:A20, D1)

With Toronto typed in D1, this gives the same result as the first formula. Change D1 to Montreal and the count updates without editing the formula.

Count numbers above or below a threshold

=COUNTIF(B2:B20, ">100")

The comparison operator and the number go together inside the quotes. ">100", "<=50", and "<>0" all work. To compare against a cell value, join the operator to the reference with &, as in =COUNTIF(B2:B20, ">"&D2).

A plain number with no operator counts exact matches: =COUNTIF(B2:B20, 250) counts cells equal to 250.

Use wildcards for partial text

* stands for any number of characters and ? stands for exactly one.

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

This counts New York, Newark, and New Orleans. =COUNTIF(A2:A20, "*ville") counts anything ending in ville. =COUNTIF(C2:C20, "Sm?th") counts both Smith and Smyth in a column of surnames.

To count cells that contain an actual asterisk or question mark, put a tilde in front of it, as in "~?".

To count with more than one condition. COUNTIFS takes pairs of ranges and criteria, and a row is counted only when every pair matches. =COUNTIFS(A2:A20, "Toronto", B2:B20, ">100") counts orders from Toronto with an amount over 100.