=COUNTIF(B2:B7,"North") counts the cells in B2:B7 that hold North. COUNTIF takes two arguments: the range to look at and the condition a cell must meet to be counted.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Region | Count | |
| 2 | Ana | North | 120 | North | 3 | |
| 3 | Ben | South | 45 | South | 2 | |
| 4 | Cara | North | 80 | |||
| 5 | Dan | East | 55 | |||
| 6 | Eva | South | 200 | |||
| 7 | Finn | North | 30 |
F2 counts the North rows. F3 does the same with the condition in a cell: change E3 to East, or change one of the regions in column B, and the count follows. A cell reference is how you build a summary table: one COUNTIF per row, each pointing at its own label.
COUNTIF syntax
=COUNTIF(range, criteria)
rangeis the cells to check. It must be a range on a sheet, such asB2:B7orB:B.criteriais the condition, written as text in double quotes, a number, or a cell reference. Every form below is a criterion:
| Criteria | Counts cells that are |
|---|---|
"North" | equal to North (any case) |
">50" | greater than 50 |
"<>North" | anything except North, blanks included |
55 | equal to the number 55 |
">"&F6 | greater than the value in F6 |
"*apple*" | text containing apple |
"" | empty |
"<>" | not empty |
COUNTIF greater than, less than or equal to
Put the comparison operator inside the quotes with the number: ">50", "<=50", "<>55". A plain number with no operator means "equal to".
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Condition | Count | |
| 2 | Ana | North | 120 | Over 50 | 4 | |
| 3 | Ben | South | 45 | 50 or less | 2 | |
| 4 | Cara | North | 80 | Exactly 55 | 1 | |
| 5 | Dan | East | 55 | Not 55 | 5 | |
| 6 | Eva | South | 200 | Limit | 100 | |
| 7 | Finn | North | 30 | Over the limit | 2 |
Four of the six sales are over 50 and two are 50 or less, so F2 and F3 always add up to the number of rows. F7 reads its limit from F6: change F6 to 50 and F7 shows the same count as F2.
COUNTIF with a cell reference
When the value you compare against is in a cell, the operator stays in quotes and the cell is joined to it with &: ">"&F6, "<="&F6, "<>"&E3. A common COUNTIF mistake is writing ">F6". Inside quotes, F6 is just two characters of text, so Excel compares every number against the text "F6" and the count is almost always 0.
For "equal to a cell" no operator is needed: while E3 holds a value, =COUNTIF(B2:B7,E3) is the same as =COUNTIF(B2:B7,"="&E3).
COUNTIF contains text: wildcards
Two wildcards work in a COUNTIF criterion. * stands for any number of characters, ? for exactly one. "*apple*" matches any cell with apple anywhere in it, "P*" any cell starting with P, "????" any text of exactly four characters.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Condition | Count | |
| 2 | Apple | contains apple | 4 | |
| 3 | Pineapple | starts with P | 3 | |
| 4 | Pear | 4 letters | 2 | |
| 5 | apple juice | equal to APPLE | 1 | |
| 6 | not Pear | 6 | ||
| 7 | Plum | empty | 1 | |
| 8 | Green apple | not empty | 6 |
Read D2 and D5 together. "*apple*" finds 4 cells, but "APPLE" without wildcards finds only 1, the cell that is exactly Apple. Without wildcards the whole cell has to match, and the match ignores case, so "APPLE", "Apple" and "apple" count the same cell.
D6 shows a trap: "<>Pear" counts 6 cells, because the empty A6 is "not Pear" too. To count only filled cells that are not Pear, use COUNTIFS with two conditions: =COUNTIFS(A2:A8,"<>Pear",A2:A8,"<>").
To use the text from a cell, join the wildcards to it: =COUNTIF(A2:A8,"*"&E2&"*"). To count a real asterisk or question mark, put a tilde before it: "~*". More patterns are on the wildcards page, and counting every cell that holds any text is on count cells with text.
COUNTIF blank and not blank
D7 and D8 in the sheet above count empty and filled cells. "" means empty and "<>" means not empty. A formula that returns an empty string (=IF(B2>0,B2,"")) looks blank and "" counts it as empty, but "<>" counts it as filled too, because the cell holds a formula. COUNTIF not blank covers the ways around that.
COUNTIF with dates
Dates are numbers in Excel, so the comparison operators work on them. Build the date with DATE, or take it from a cell, and join it to the operator with &. Never type a date inside the quotes in your own regional format: ">3/1/2026" means March 1 in the US and January 3 in most of Europe.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Date | Condition | Count | |
| 2 | Ana | 2026-01-05 | From Feb 1 | 4 | |
| 3 | Ben | 2026-01-12 | Cutoff | 2026-03-01 | |
| 4 | Cara | 2026-02-03 | Before the cutoff | 4 | |
| 5 | Dan | 2026-02-18 | On Jan 12 | 1 | |
| 6 | Eva | 2026-03-02 | |||
| 7 | Finn | 2026-03-20 |
E2 counts the four dates on or after February 1. E4 counts the dates before the cutoff in E3: change E3 to 2026-02-01 and it drops to 2. Counting between two dates needs two conditions, which is COUNTIFS: =COUNTIFS(B2:B7,">="&DATE(2026,2,1),B2:B7,"<"&DATE(2026,3,1)).
Count duplicates with COUNTIF
COUNTIF of the whole list against one of its own cells tells you how many times that value appears. Anything above 1 is a duplicate. Lock the list with $ so it stays put while the formula is filled down.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Times | Duplicate? |
| 2 | Ana | 2 | TRUE |
| 3 | Ben | 2 | TRUE |
| 4 | Cara | 1 | FALSE |
| 5 | Ana | 2 | TRUE |
| 6 | Dan | 1 | FALSE |
| 7 | Ben | 2 | TRUE |
| 8 | Eva | 1 | FALSE |
Ana and Ben appear twice, so their rows show 2 and TRUE, and the conditional formatting rule =COUNTIF($A$2:$A$8,A2)>1 on A2:A8 highlights them. Change the last name to Cara and Cara lights up too.
=COUNTIF($A$2:A2,A2)>1 (only the start of the range locked) counts each name from the top down to its own row, so it marks the second Ana but not the first. That version flags the copies you would delete and keeps the first of each. The full method is on remove duplicates.
Practice: COUNTIF with a cell
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Amount | Setting | Value | |
| 2 | A-101 | 120 | Limit | 100 | |
| 3 | A-102 | 45 | Orders over limit | ||
| 4 | A-103 | 310 | |||
| 5 | A-104 | 100 | |||
| 6 | A-105 | 85 | |||
| 7 | A-106 | 140 | |||
| 8 | A-107 | 99 |
Your turn: Count the orders in B2:B8 that are larger than the limit in E2. Write the formula in E3.
Change E2 after you pass: the count should move with it. An order of exactly 100 is not "larger than" 100.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Setting | Value | |
| 2 | Apple | Word | apple | |
| 3 | Pear juice | Matches | ||
| 4 | Pineapple | |||
| 5 | Banana | |||
| 6 | Green apple | |||
| 7 | Pear | |||
| 8 | Apple cider |
Your turn: Count the products in A2:A8 that contain the word in D2 anywhere in the name. Write the formula in D3.
COUNTIF with multiple criteria
COUNTIF takes one condition. For two or more there are two cases.
All conditions must be true (North and over 50): use COUNTIFS, which takes range and criteria pairs.
=COUNTIFS(B2:B7,"North",C2:C7,">50")
Either condition may be true (North or South): add two COUNTIFs, or give one COUNTIF a list in braces and SUM the results.
=COUNTIF(B2:B7,"North")+COUNTIF(B2:B7,"South")
=SUM(COUNTIF(B2:B7,{"North","South"}))
Do not use the OR version on one cell that could match both conditions ("*apple*" or "*green*"): Green apple would be counted twice. For overlapping conditions, =SUMPRODUCT(--((ISNUMBER(SEARCH("apple",A2:A8))+ISNUMBER(SEARCH("green",A2:A8)))>0)) counts each cell once.
Count with case sensitivity
COUNTIF never tells Apple from apple. When case matters, compare with EXACT, which is case-sensitive, and add up the TRUE results:
=SUMPRODUCT(--EXACT(A2:A8,"Apple"))
The -- turns TRUE and FALSE into 1 and 0. SUMPRODUCT is the general tool for conditions COUNTIF cannot express.
Why COUNTIF returns 0 or a wrong count
- The operator and the cell are both inside the quotes.
">F6"compares against the text F6. Write">"&F6. - Extra spaces in the data. "North " with a trailing space is not equal to "North". Count with
"North*"to check, then clean the column with TRIM. - Numbers stored as text. A criterion like
">50"only compares real numbers, so numbers typed as text (left-aligned, often with a green triangle) are skipped. Convert them first; see numbers stored as text. - The range points at another workbook that is closed. COUNTIF returns
#VALUE!until that workbook is open. SUMPRODUCT does not have this limit. - Text longer than 255 characters. COUNTIF cannot match strings longer than 255 characters. Compare directly instead:
=SUMPRODUCT(--(A2:A8=E2)).
Frequently Asked Questions
How do I use COUNTIF in Excel?
Give it a range and a condition: =COUNTIF(B2:B7,"North") counts the cells in B2:B7 equal to North, and =COUNTIF(C2:C7,">50") counts the numbers above 50. Text and comparisons go in double quotes.
How do I use COUNTIF with a cell reference?
Join the operator and the cell with &: =COUNTIF(C2:C7,">"&F6) counts values greater than the number in F6. Writing ">F6" inside the quotes compares against the text F6 and usually returns 0.
Is COUNTIF case sensitive?
No. "apple", "Apple" and "APPLE" all count the same cells. For a case-sensitive count use =SUMPRODUCT(--EXACT(A2:A8,"Apple")).
How do I use COUNTIF with multiple criteria?
For conditions that must all be true, use COUNTIFS: =COUNTIFS(B2:B7,"North",C2:C7,">50"). For either of two values, add two COUNTIFs: =COUNTIF(B2:B7,"North")+COUNTIF(B2:B7,"South").
