COUNTIF in Excel: how to use it
COUNTIF tells you how many cells match what you ask for, such as =COUNTIF(A2:A6,"Leeds").
Try the first exercises for freeIn the lesson Counting with COUNTIF
What does COUNTIF do?
COUNTIF counts how many cells in a range meet one condition. You give it two things: where Excel should look, and what has to be in a cell for it to count.
The condition can be a text, such as "Leeds", a number, or a comparison such as ">500". Capital letters make no difference, and with wildcards such as * you can also count on part of a text.
When do you use COUNTIF?
- How many orders come from Leeds: =COUNTIF(A2:A100,"Leeds").
- How many revenue figures are over 500: =COUNTIF(C2:C100,">500").
- How many students passed, with a score of at least 10: =COUNTIF(B2:B30,">=10").
- Whether a name appears twice in a list: =COUNTIF($A$2:$A$50,A2)>1.
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =COUNTIF(C2:C6,">=400")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
COUNTIF | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | COUNTIF |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
range | The cells you want to look at. | C2:C6 |
criteria | What they should contain: "Leeds", 100 or ">500". | ">=400" |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. |
COUNTIF step by step
E1 contains =COUNTIF(C2:C6,">=400"). Counts the rows with revenue of at least 400. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click the cell where the count should go, here E1, and type the function with a bracket.
=COUNTIF( - Select the cells Excel should look in: the revenue in C2 to C6. Type a comma.
=COUNTIF(C2:C6, - Type the condition in quotation marks: at least 400 is written as ">=400".
=COUNTIF(C2:C6,">=400" - Close the bracket and press Enter: three revenue figures are at least 400.
=COUNTIF(C2:C6,">=400")
More examples
Counting on a text
E1 contains =COUNTIF(A2:A6,"Leeds"). Two rows are from Leeds.
A limit from a cell
F2 contains =COUNTIF(C2:C6,">"&F1). The limit is in F1. With & you join the > sign to that cell. Change F1 to 300, and the count adjusts.
Everything except
E1 contains =COUNTIF(B2:B6,"<>Laptop"). With <> you count everything that isn't a laptop: two monitors and a mouse.
Writing criteria
Text and comparisons go in quotation marks: "Leeds", ">500" or "<>Leeds". To compare with a cell, join it to the sign with &: ">"&E1.
Criteria you'll use a lot
You count how often a name appears with =COUNTIF(A2:A50,"Leeds"). Everything except Leeds is "<>Leeds". Amounts above a limit are ">500". If the name comes from a cell, simply write =COUNTIF(A2:A50,E1), without quotation marks.
Spotting duplicates
=COUNTIF($A$2:$A$50,A2)>1 gives TRUE on every row whose value appears more than once. Fill the formula down and filter on TRUE. That way you see straight away which rows to check.
More than one condition
If a row has to meet several things at once, use COUNTIFS: =COUNTIFS(A2:A50,"Leeds",C2:C50,">500") counts the rows from Leeds with an amount above 500.
Where it often goes wrong
- >500 without quotation marks is an error.
- Upper and lower case make no difference, but invisible spaces do.
Practise this in the lesson Counting with COUNTIF. The first exercises are free.
Frequently asked questions
Can I count on part of a text?
Yes, with wildcards: "*bike*" counts everything that contains bike, such as City bike.