COUNTIF in Excel
COUNTIF counts cells that match a single condition — a repeated order ID, a passing score, or a status such as Shipped. COUNTIFS is the version for two or more conditions. When the job is counting text cells rather than numbers, a wildcard criterion does the work.
COUNTIF Syntax & The 2 Arguments
COUNTIF walks range and counts the cells that meet criteria. It counts; it does not add. One condition, one number.
The cells to test (a status column B2:B6, or order IDs in A2:A11). COUNTIF checks every cell here.
The condition: quote text ("Shipped"), leave numbers bare (60), quote operators (">=60"), join a cell (">=" & E1), or use a wildcard ("*apple*" or "*").
COUNTIF example: shipped orders
Column B lists order status. This COUNTIF example counts only the Shipped rows — one range, one criterion.
| Order | Status |
|---|---|
| ORD-1 | Shipped |
| ORD-2 | Pending |
| ORD-3 | Shipped |
| ORD-4 | Cancelled |
| ORD-5 | Shipped |
COUNTIF checks B2:B6 for Shipped and returns 3. Switch to COUNTIFS when a second condition appears. For counting text cells instead of one word, use COUNTIF(A2:A6,"*") — the asterisk matches text and skips numbers and blanks.
COUNTIF vs COUNTIFS
COUNTIF takes one range/criteria pair. COUNTIFS stacks more pairs, and a row counts only when every pair matches. There is no sum range — do not copy the SUMIFS habit of putting a number column first.
| Region | Status |
|---|---|
| North | Shipped |
| South | Shipped |
| North | Pending |
| North | Shipped |
Two rows are both North and Shipped. COUNTIF(B2:B5, "Shipped") returns 3 because the South row counts too. Use COUNTIFS for the extra condition and leave single-condition counts on COUNTIF.
Counting text cells
COUNT counts numbers. COUNTA counts every non-blank. For counting text cells, the criterion is the wildcard "*". The asterisk matches text and skips numbers and truly blank cells.
| Cell | Value |
|---|---|
| A2 | apple |
| A3 | 12 |
| A4 | — |
| A5 | banana bread |
| A6 | 4 |
The result is 2: apple and banana bread. 12 and 4 are numbers, and A4 is blank, so none of them count. Use "?*" when the cell must contain at least one character, or "*bread*" to count cells that contain a word (1 in this range). Matching is not case-sensitive. A formula that returns "" is text to ISTEXT, but COUNTIF with "*" does not count it.
4 COUNTIF Gotchas
1. Criteria quotes
Text criteria must be quoted: "Shipped". Numbers are bare: 60. A comparison is one string: ">=60". Writing >=60 bare is a syntax error. When the threshold lives in a cell, join it: ">=" & E1.
2. Writing COUNTIFS like SUMIFS
SUMIFS starts with a sum range. COUNTIFS does not — the first argument is the first criteria range. An extra number column shifts the pairs and returns 0 or #VALUE!.
3. Wildcards and case
COUNTIF ignores case (shipped = Shipped). "North*" also matches Northeast. Leave the asterisk off when you need an exact value.
4. Why the result is 0
Hidden spaces around the criterion (TRIM), numbers stored as text, or "*" pointed at a column of numbers. COUNTIF does not throw when nothing matches — it returns 0.
Excel vs Google Sheets
Microsoft Excel (365 / Desktop)
Excel COUNTIF and COUNTIFS accept cross-sheet references: =COUNTIF(Sheet2!B:B, "Shipped"). COUNTIFS has been available since Excel 2007. Whole-column refs are convenient and slower on large sheets than a bounded range.
Google Sheets
Google Sheets uses the same syntax and the same wildcard rules. COUNTIFS is still AND logic. QUERY can group counts, but a single condition still belongs in COUNTIF.
COUNTIF exercises for this guide
COUNTIF exercises
Duplicate order IDs use an exact text criterion. Passing scores put the comparison operator inside the criteria string.
FAQ
How does COUNTIF work in Excel?
=COUNTIF(range, criteria) walks range and counts every cell that meets criteria. Example: =COUNTIF(B2:B6, "Shipped") returns how many statuses equal Shipped. Text criteria and operators such as ">=60" stay inside quotes.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF accepts one condition. COUNTIFS accepts two or more, and a row counts only when every pair matches: =COUNTIFS(A2:A5, "North", B2:B5, "Shipped"). Unlike SUMIFS, COUNTIFS has no sum range — each argument is a range/criteria pair.
How do I count cells that contain text?
Use a wildcard: =COUNTIF(A2:A10, "*") is the usual way of counting text cells. Numbers and blanks do not match the asterisk. To require at least one character, use "?*". To count cells containing a word, use "*apple*". Matching is not case-sensitive.
How do I count duplicates with COUNTIF?
A helper column =COUNTIF($A$2:$A$11, A2) returns how many times the value in A2 appears. Values of 2 or more are duplicates. The same test, =COUNTIF($A$2:$A$11, A2)>1, is the custom formula behind highlight-duplicates rules.
Why does COUNTIF return 0?
Typical causes: text criteria without quotes, a number stored as text when the criterion is numeric, hidden spaces (clean them with TRIM), or a wildcard that does not match the way you expect. COUNTIF does not throw — it just counts zero matches.