Statistical
COUNTIF in Excel:syntax, examples and errors
COUNTIF counts how many cells in a range meet a condition. In Spanish Excel it is called CONTAR.SI. Syntax: =COUNTIF(range, criteria).
Syntax
=COUNTIF(range, criteria)In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
range | Required. The cells you want to check. Example: B2:B9. |
criteria | Required. The condition, in quotes if it is text or has an operator. Examples: "Austin", ">100", F2, ">"&F1. |
How do you use COUNTIF? A step-by-step example
COUNTIF counts the rows that meet a condition. In this example you have 8 orders from an online store and you want to know how many are from Austin.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | City | Amount | Status |
| 2 | N-201 | Austin | 85.00 | Paid |
| 3 | N-202 | Denver | 240.00 | Pending |
| 4 | N-203 | Austin | 132.50 | Paid |
| 5 | N-204 | Boston | 60.00 | Pending |
| 6 | N-205 | Denver | 410.00 | Paid |
| 7 | N-206 | Austin | 99.90 | Pending |
| 8 | N-207 | New York | 175.00 | Paid |
| 9 | N-208 | Denver␣ | 55.00 | Paid |
Cell B9 has a space at the end (marked with ␣). You cannot see it in Excel, and it matters below.
=COUNTIF(B2:B9,"Austin") returns 3.
B2:B9is where you look: the column of cities."Austin"is the condition. Because it is text, it goes in quotes.- COUNTIF goes through the 8 cells and counts the ones equal to Austin.
What can you put in the criteria?
| You want to count | Criteria | Formula | Result |
|---|---|---|---|
| An exact text | "Austin" | =COUNTIF(B2:B9,"Austin") | 3 |
| What a cell says | F2 | =COUNTIF(B2:B9,F2), with Austin in F2 | 3 |
| Greater than a number | ">100" | =COUNTIF(C2:C9,">100") | 4 |
| Greater than a cell | ">"&F1 | =COUNTIF(C2:C9,">"&F1), with 100 in F1 | 4 |
| Not equal to | "<>Paid" | =COUNTIF(D2:D9,"<>Paid") | 3 |
| Starts with | "Denver*" | =COUNTIF(B2:B9,"Denver*") | 3 |
| Contains | "*York*" | =COUNTIF(B2:B9,"*York*") | 1 |
The asterisk * stands for any number of characters and the question mark ? for exactly one. That is why "Denver*" also counts the cell with the extra space.
COUNTIF or COUNTIFS?
COUNTIF takes one condition. For two or more (Austin orders and pending ones) use COUNTIFS, which is in every version since Excel 2007.
| COUNTIF | COUNTIFS | |
|---|---|---|
| Conditions | 1 | Up to 127 |
| Order of arguments | range, criteria | range1, criteria1, range2, criteria2... |
| Example | =COUNTIF(B2:B9,"Austin") | =COUNTIFS(B2:B9,"Austin",D2:D9,"Pending") |
| Result with this data | 3 | 1 |
If you want to add the amounts instead of counting rows, that is SUMIF, with the same criteria. To label each row instead of counting, use IF. To pull a value from a table, use VLOOKUP.
How does COUNTIF show up in an Excel test?
In a job interview a summary is common: a list of cities in F2:F5 and "count the orders for each one". The answer is =COUNTIF($B$2:$B$9,F2), dragged down. What they look for: the criteria is the cell, not the text typed by hand, and the range is locked.
In a skills test:
What does
=COUNTIF(C2:C9,">=100")count? a) The cells with the text >=100 · b) The cells with a value of 100 or more · c) The cells greater than 100 · d) The first 100 cells
Answer: b. With this data it returns 4.
Practice COUNTIF
The English exercises are on the way. Until then, copy the orders table into a blank sheet, try each formula in the criteria table, and then add a second Austin order to see the count change.
Available in: all versions of Excel. COUNTIFS, since Excel 2007. Source: Microsoft: COUNTIF function. Results calculated with Formulo's engine.
Common mistakes
- It counts fewer than you can see
- A cell has an extra space ("Denver "). To Excel that is a different text. This page has 3 Denver orders, but =COUNTIF(B2:B9,"Denver") returns 2. Clean the column with TRIM or count with a wildcard: =COUNTIF(B2:B9,"Denver*") returns 3.
- It returns 0 with a criteria that uses a cell
- You wrote the cell inside the quotes. =COUNTIF(C2:C9,">F1") searches for the text F1 and returns 0. Take the cell out and join it with &: =COUNTIF(C2:C9,">"&F1) returns 4.
- You need two conditions
- COUNTIF takes only one. To count Austin orders that are still pending, use COUNTIFS.
- Numbers stored as text do not count with >
- A criteria like ">100" only compares numbers. If the amounts are stored as text, they do not count. Convert them to numbers.
Frequently asked questions
What is COUNTIF called in Spanish?
CONTAR.SI. In a Spanish Excel: =CONTAR.SI(B2:B9;"Austin").
Is COUNTIF case-sensitive?
No. =COUNTIF(B2:B9,"austin") also counts the cells that say Austin.
How do I count the cells that are not empty?
With COUNTA: =COUNTA(B2:B9). COUNTIF can do it too: =COUNTIF(B2:B9,"<>").
How do I count the cells that contain a word?
With asterisks on both sides: =COUNTIF(B2:B9,"*York*") counts the cells that contain York anywhere. In the example, it returns 1.
How do I get a percentage with COUNTIF?
Divide by the total: =COUNTIF(B2:B9,"Austin")/COUNTA(B2:B9) returns 0.375. Format it as a percentage and it shows 37.5%.
What is the difference between COUNT, COUNTA and COUNTIF?
COUNT counts the cells with numbers. COUNTA counts the ones that are not empty. COUNTIF counts the ones that meet a condition.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.