COUNTIF Function in Excel and Google Sheets
Purpose
Count the number of cells in a range that meet a single condition
Return value
A number representing how many cells in range match criteria
COUNTIF answers the question that every spreadsheet user eventually needs answered: how many times does this appear? How many rows are marked as Complete? How many sales exceeded the monthly target? How many customers are in the Premium tier? How many invoices are overdue? How many products are below the reorder quantity? Every one of these is a one-condition counting question and COUNTIF handles all of them in a single formula without filtering, without sorting, and without any manual counting. It works on text, numbers, dates, and wildcard patterns, it is case-insensitive by default, and it updates automatically every time the underlying data changes. I use COUNTIF constantly — in dashboards for summary counts, in data quality checks to verify no duplicates or missing values, in conditional formatting rules to highlight cells based on how many times a value appears, and in validation formulas that prevent duplicate entries in data entry forms.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=COUNTIF(range, criteria)
Arguments
| Argument | Required | Description |
|---|---|---|
| range | Required | The range of cells to count. Can be a column, row, or rectangular range. COUNTIF checks every cell in this range against criteria. |
| criteria | Required | The condition to match. Text in quotes: "Sales". Numbers: 100. Comparisons: ">500", "<>". Wildcards: "Sales*", "*error*". Cell references: A1. Operator with reference: ">"&A1. Not case-sensitive. |
How it works
COUNTIF examines every cell in range and checks whether it satisfies criteria. For each cell that matches it adds 1 to an internal counter, then returns the final count after checking every row. Text comparisons are case-insensitive — "Sales", "sales", "SALES", and "SaLeS" all count as the same match. When using comparison operators in the criteria argument, the operator and value must be wrapped together inside a single set of quotes: ">500" is correct, >","500" is not. When comparing against a value stored in another cell, concatenate the operator string with the cell reference using the & operator: ">"&B1 means greater than whatever value is in cell B1. This makes the criteria dynamic — change B1 and the count updates immediately. For partial text matching, use * as a wildcard that matches any sequence of characters. The criteria "*Pro*" matches any cell containing the characters Pro anywhere in the text. For counting non-blank cells specifically, use "<>" as the criteria — this matches any cell that is not empty.
Examples
1
Count how many employees are in the Sales department.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Department | Count |
| 2 | Alice | Sales | =COUNTIF(B2:B11,"Sales") |
| 3 | Bob | Marketing | |
| 4 | Carol | Sales | |
| 5 | David | Engineering | |
| 6 | Eve | Marketing | |
| 7 | Frank | HR | |
| 8 | Grace | Sales | |
| 9 | Hank | Engineering | |
| 10 | Iris | HR | |
| 11 | James | Sales |
Row 2: 4
— Alice, Carol, Grace, and James are in Sales — four rows match.
The count updates automatically if you change any department label. COUNTIF is case-insensitive so SALES, Sales, and sales all count as matches.
2
Count how many transactions exceed 1000.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Category | Amount |
| 2 | 2024-01-05 | Travel | 850 |
| 3 | 2024-01-08 | Software | 1299 |
| 4 | 2024-01-12 | Travel | 1200 |
| 5 | 2024-01-15 | Office | 75 |
| 6 | 2024-01-18 | Travel | 620 |
| 7 | 2024-01-22 | Software | 450 |
| 8 | 2024-01-25 | Training | 1980 |
| 9 | 2024-01-28 | Office | 45 |
| 10 | 2024-01-30 | Travel | 1100 |
Row 2: 4
— 1299, 1200, 1980, and 1100 all exceed 1000 — four transactions match.
The comparison operator and value must be inside quotes as a single string: ">1000". To make the threshold dynamic: =COUNTIF(C2:C10,">"&E1) where E1 contains 1000.
3
Count how many product names contain the word "Pro" anywhere.
| A | |
|---|---|
| 1 | Product Name |
| 2 | Wireless Mouse |
| 3 | USB Keyboard Pro |
| 4 | Monitor Stand |
| 5 | Laptop Bag Pro |
| 6 | HDMI Cable |
| 7 | Webcam Pro HD |
| 8 | Desk Lamp |
| 9 | Headset Pro |
Row 2: 4
— USB Keyboard Pro, Laptop Bag Pro, Webcam Pro HD, and Headset Pro all contain "Pro".
Asterisks before and after "Pro" mean COUNTIF looks for Pro anywhere in the cell. Without asterisks it would only match cells containing exactly "Pro" and nothing else.
Common use cases
1. Count how many invoices in a status column are currently marked as Overdue
=COUNTIF(D:D,"Overdue")
2. Count how many items in an inventory list have a quantity below the reorder level stored in another cell
=COUNTIF(C:C,"<"&D2)
3. Count how many cells in a data column are not blank to verify completeness of a data entry form
=COUNTIF(A:A,"<>")
4. Count how many times a specific value appears in a list to detect and highlight duplicate entries
=COUNTIF($A$2:$A$100,A2)
5. Count how many product names in a catalogue contain a specific model designation anywhere in the name
=COUNTIF(A:A,"*Pro*")
Common errors
Returns zero even though matching values exist in the range
Data type mismatch is almost always the cause. Numbers stored as text cannot be matched by numeric criteria and vice versa. The cells look identical on screen but are fundamentally different data types. Also check for leading or trailing spaces that make a text string look correct but fail exact matching.
Fix: Confirm cell alignment — real numbers right-align by default in Excel. Use TRIM to remove spaces from the data. Use VALUE to convert text-numbers to real numbers. Or use the criteria as text: use "="&A1 to match the text representation of whatever is in A1.
Count is higher or lower than expected with no apparent reason
A wildcard pattern is too broad and matching unintended cells, or the criteria has a trailing space making it match a slightly different set of cells than intended. Also check whether COUNTIF is counting header rows if the range includes row 1.
Fix: Test the wildcard criteria by listing matches with a filter first. Remove any accidental spaces from the criteria text. Start the range at row 2 to exclude the header if applicable.
Error when trying to count across multiple ranges
COUNTIF only accepts a single range argument. Attempting to pass a union of ranges using commas inside the range argument causes an error.
Fix: Add multiple COUNTIF calls together in one formula: =COUNTIF(A:A,"Yes")+COUNTIF(C:C,"Yes"). Each COUNTIF covers one range independently.
Tips and variations
Use COUNTIF inside conditional formatting to highlight duplicate values automatically
Apply a conditional formatting rule to an entire column using the formula =COUNTIF($A$2:$A$100,A2)>1. This highlights every cell whose value appears more than once in the column. Lock the range with dollar signs and leave the cell reference unlocked so each row checks whether its own value is duplicated.
=COUNTIF($A$2:$A$100,A2)>1
Make the criteria dynamic by referencing a cell instead of hardcoding a value
Replacing a hardcoded text string like "Sales" with a cell reference like E1 lets you change what you are counting by typing a new value in E1 rather than editing the formula. This pattern is the foundation of interactive dashboards where a single dropdown or input cell controls multiple summary counts.
=COUNTIF(B:B,E1)
Count non-blank cells using "<>" as the criteria for fast data completeness checks
The criteria "<>" means not equal to blank and matches every cell that contains any value — including zero, text, and dates. It is faster to type than COUNTA and makes the intent of the formula clear to anyone reading it. Use it in data quality checks to verify that all required fields in a form or import have been filled.
=COUNTIF(A:A,"<>")
Excel vs Google Sheets
Excel vs Google Sheets
COUNTIF works identically in Excel and Google Sheets. Same two arguments, same criteria syntax, same wildcard support, same case-insensitivity. Formulas copy between the two without any changes.
Frequently asked questions
COUNTIF counts the number of cells in a range that satisfy a single condition. It is the counting equivalent of SUMIF. Use it to count how many times a specific value appears in a column, how many cells contain a number above or below a threshold, how many cells contain partial text matching a pattern, how many dates fall in a particular period, or how many cells in a range are not blank.
Use wildcard characters in the criteria argument. The asterisk * matches any number of characters in any position. The question mark ? matches exactly one character. =COUNTIF(A:A,"*sales*") counts every cell in column A that contains the word sales anywhere — at the start, in the middle, or at the end of the cell content. =COUNTIF(A:A,"sales*") counts only cells that start with sales. Both wildcards can be combined in a single criteria string.
COUNTIF accepts exactly one range and one criteria — it counts cells that match a single condition. COUNTIFS accepts multiple range-criteria pairs and counts cells where all conditions are simultaneously true — AND logic. If you need to count rows where the department is Sales AND the region is North, you need COUNTIFS with two pairs of arguments. For a single condition, COUNTIF is simpler and more concise to write.
The most common cause is a data type mismatch. Numbers stored as text look identical to real numbers on screen but COUNTIF treats them as completely different values. A number 42 will not match the text string 42 even though both display the same way in a cell. The telltale sign is left-aligned cells in a column that should contain numbers — left alignment indicates text formatting. Extra spaces from data imports are another frequent cause.
COUNTIF only accepts one range argument. To count matches across multiple non-adjacent ranges, add separate COUNTIF calls together in one formula: =COUNTIF(A:A,"Yes")+COUNTIF(C:C,"Yes")+COUNTIF(E:E,"Yes"). For multiple conditions applied to the same range simultaneously, use COUNTIFS instead.