COUNTIF in Google Sheets

Counts the cells in a range that meet one condition. Text conditions ignore upper and lower case.

In Google Sheets

COUNTIF takes the same two arguments in Google Sheets and understands the same operators and wildcards.

How to use COUNTIF

COUNTIF answers the question "how many?" for one condition. Think of ticking names off a list: how many say North, how many sales are over 300, how many cells are still empty.

You give it the range to look through and the condition: =COUNTIF(A2:A10,"North"). Text matching ignores upper and lower case, and the condition has to match the whole cell unless you use wildcards. "*north*" finds north anywhere in the cell and "N*" finds everything that starts with N. Numbers and dates use a comparison in quotes, such as ">=300". To compare with a cell, join the two with &, like "<>"&D1.

Two shortcuts are worth knowing: "<>" counts the cells that are not empty, and "" counts the empty ones.

There is no sum range here, because the range you check is also the range you count. COUNTIF holds one condition. For several conditions, or for values between two limits, use COUNTIFS. To add up amounts instead of counting rows, use SUMIF.

Syntax

COUNTIF(range, criteria)
range
The cells to count.
criteria
The condition, such as "North", ">=300", "*shoe*" or a cell.

Examples

E1=COUNTIF(A2:A6,"North")
ABCDE
1RegionProductSales3
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

In plain words: Count the cells in A2:A6 that say North.

E1=COUNTIF(C2:C6,">=300")
ABCDE
1RegionProductSales3
2NorthShoes500
3SouthShoes300
4NorthBags250
5NorthShoes420
6SouthBags180

Common COUNTIF mistakes

  • COUNTIF returns 0 for text that is clearly in the cells

    A text condition has to match the whole cell. To find a word anywhere inside the text, put asterisks around it: =COUNTIF(A2:A6,"*north*"). Extra spaces at the start or end of a cell also break an exact match.

  • A condition like ">E1" counts nothing

    Inside quotes, E1 is just the two letters E1. Join the operator and the cell with &: =COUNTIF(C2:C6,">"&E1).

  • COUNTIF shows an error when I give it two conditions

    COUNTIF takes one condition, so a second pair of range and condition is too many arguments. Use COUNTIFS and repeat the range: =COUNTIFS(C2:C6,">=200",C2:C6,"<=400").

  • Counting by date gives the wrong number

    A date typed inside quotes depends on the date settings of the file, and dates stored as text are not counted as dates. Build the date with DATE: =COUNTIF(A2:A100,">"&DATE(2024,1,1)).

Practice COUNTIF

Related functions