COUNTIF counts cells that meet one condition in Google Sheets. The condition can be an exact label, a numeric comparison, or a text pattern.
Its text matching is case-insensitive. Use COUNTIFS for several conditions that must all hold, or a case-sensitive alternative when uppercase and lowercase labels must stay distinct.
COUNTIF function syntax
=COUNTIF(range, criterion)- range: the cells to test.
- criterion: text, a number, a comparison in quotation marks, or a cell reference.
The Google COUNTIF reference describes the function arguments.
Example data
Enter this dataset starting in A1. Leave cells marked “leave empty” empty; type any displayed formula as a formula. Keep a separate blank area for the results.
| Region | Sales | Product |
|---|---|---|
| East | 200 | Pro Camera |
| West | 120 | Mouse |
| east | 300 | Pro Keyboard |
| North | 80 | Cable |
| East | 100 | Webcam Pro |
| West | 0 | Cable |
The examples below use this dataset unless the formula supplies its own values. Array results need enough empty cells to expand. The formulas use commas as argument separators. Your spreadsheet locale may require semicolons.
Count an exact text label
East and east both match the criterion East, giving three matching records.
=COUNTIF(A2:A7,"East")Result: 3
Count numbers above a threshold
The quoted comparison counts sales strictly greater than 100.
=COUNTIF(B2:B7,">100")Result: 3
Join the operator to B5 with & to use its value, 80, as the threshold.
=COUNTIF(B2:B7,">"&B5)Result: 4
Count text containing a word or character pattern
An asterisk on both sides finds Pro anywhere in the product name.
=COUNTIF(C2:C7,"*Pro*")Result: 3
A question mark matches exactly one character, so ?able matches Cable.
=COUNTIF(C2:C7,"?able")Result: 2
A tilde escapes the wildcard. The criterion ~* counts a literal single asterisk.
=COUNTIF({"*";"A";"**"},"~*")Result: 1
Build counts for a list of criteria
ARRAYFORMULA returns separate counts for East, West, and North in that order. The result expands down three cells.
=ARRAYFORMULA(COUNTIF(A2:A7,{"East";"West";"North"}))| 3 |
| 2 |
| 1 |
Count case-sensitive matches
EXACT distinguishes East from east. SUMPRODUCT adds the TRUE results after converting them to numbers, giving two exact-case matches.
=SUMPRODUCT(--EXACT(A2:A7,"East"))Result: 2
Require more than one condition
COUNTIFS counts rows where the region is East and sales exceed 100. Both conditions must be true for the same row.
=COUNTIFS(A2:A7,"East",B2:B7,">100")Result: 2
Count an empty-string result
The empty-string criterion matches the empty text in this array. Zero is not counted as empty.
=COUNTIF({"";"x";0},"")Result: 1
Other Google Sheets articles you may also like