FormuladrillCOUNTIF
COUNTIF(range, criteria) counts the cells in a range that meet one condition, such as "Shipped", ">=70" or "*error*". Text matching ignores case, and wildcards ? and * are allowed. For several conditions use COUNTIFS.
Syntax
COUNTIF(range, criteria)| Part | What it is |
|---|---|
range | The cells to test. |
criteria | A value, a cell reference or a text condition like ">100", "<>", "*Ltd". |
Examples
=COUNTIF(B2:B100, "Shipped")Result: Number of rows whose status is Shipped
=COUNTIF(C2:C100, ">=70")Result: Number of scores of 70 or more
The operator and number go inside quotes as one text string.
=COUNTIF($A$2:$A$100, A2) > 1Result: TRUE if the value in A2 appears more than once in the list
Fill down to flag duplicates.
=COUNTIF(A2:A100, "<>")Result: Number of non-empty cells in A2:A100
Common mistakes
- Count of 0 for ">" & E2 written as ">E2": the cell reference ends up inside the text. Use ">" & E2.
- COUNTIF(A:A, "102") and COUNTIF(A:A, 102) count different things when some cells are text and some numbers. Make the column consistent.
- Cells with a formula that returns "" are not blank: COUNTIF(range, "") counts them, COUNTBLANK counts them too, but COUNTA counts them as filled.
- Searching for a value that contains * or ? needs a tilde: COUNTIF(A:A, "~*") counts actual asterisks.
Practice COUNTIF
These drills check your answer on live data, free, no account needed.
- Count the shipped ordersCount how many orders have the status "Shipped". Put the answer in E2.
- Scores above a barCount how many scores are strictly above the threshold in E1. Put the answer in E2.
Questions
How do I COUNTIF cells that contain specific text?
Use wildcards: =COUNTIF(A2:A100, "*invoice*") counts cells that contain invoice anywhere.
How do I count cells that are not blank?
=COUNTIF(A2:A100, "<>") or =COUNTA(A2:A100). COUNTA also counts cells with an empty-string formula result; COUNTIF with "<>" does too.
Is COUNTIF case sensitive?
No. For a case-sensitive count use =SUMPRODUCT(--EXACT(A2:A100, "Apple")).
Related
- COUNTIFSHow to use COUNTIFS in Excel to count rows where two or more conditions are all true, with examples for text plus number, date ranges and not-equal.
- SUMIFHow to use SUMIF in Excel to add up numbers that match one criterion, with examples for text, greater than, cell references and wildcards, and common fixes.
- COUNT, COUNTA and COUNTBLANKHow COUNT, COUNTA and COUNTBLANK differ in Excel: COUNT counts numbers, COUNTA any non-empty cell, COUNTBLANK empty cells, with examples and the blank trap.
- SUMPRODUCTHow the Excel SUMPRODUCT function multiplies ranges and sums the result, with examples for quantity times price, weighted average and sums with conditions.