How many customers in the list are from a particular city? How many sales are greater than 55? Counting such things by hand takes time and invites mistakes. Excel's COUNTIF function does the job with a single formula. In this article we explain how COUNTIF is written and walk through examples based on Microsoft Support's official guide.
What is COUNTIF?
According to Microsoft, COUNTIF is one of the statistical functions and counts the number of cells that meet a criterion. For example, you can use it to count how many times a particular city appears in a customer list.
Remember the difference: SUMIF adds up values that meet a condition, while COUNTIF gives the number of cells that meet it.
Where is COUNTIF useful in everyday work?
A few typical situations:
- counting how many customers from each city are in a list;
- finding the number of sales above a certain amount;
- checking how many times the same name is repeated in a list;
- counting non-empty cells that contain text.
The logic is the same each time: first you specify the range, then the condition.
Syntax: two arguments
The function is written like this:
=COUNTIF(range, criteria)
| Argument | What is it? |
|---|---|
| range (required) | The group of cells you want to count. It can contain numbers, arrays, a named range or references that contain numbers. |
| criteria (required) | A number, expression, cell reference or text that determines which cells are counted. For example: 32, ">32", B4 or "apples". |
COUNTIF with examples
The examples below come from Microsoft's guide. Assume that cells A2:A5 contain fruit names (apples, oranges, peaches, apples) and cells B2:B5 contain numbers.
| Formula | What it does | Result |
|---|---|---|
| =COUNTIF(A2:A5,"apples") | Counts cells containing "apples" in A2:A5 | 2 |
| =COUNTIF(A2:A5,A4) | Counts cells equal to the value in A4 (peaches) | 1 |
| =COUNTIF(B2:B5,">55") | Counts values greater than 55 in B2:B5 | 2 |
| =COUNTIF(B2:B5,"<>"&B4) | Counts cells not equal to the value in B4 (75) | 3 |
| =COUNTIF(A2:A5,"*") | Counts cells containing any text | 4 |
In the fourth example, the & sign merges the "not equal to" operator ("<>") with the value of cell B4, so the formula works as =COUNTIF(B2:B5,"<>75").
How to count with two conditions?
Microsoft shows two approaches. The first is to write COUNTIF twice and add the results:
=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)
This formula counts cells with "apples" (A2) and "oranges" (A3) together; the result is 3. Each expression has one condition. The second way is the COUNTIFS function, which is designed for multiple conditions.
Wildcards: ? and *
You can use wildcard characters in the condition:
- ? — matches any single character;
- \* — matches any sequence of characters;
- to find an actual question mark or asterisk, type a ~ (tilde) in front of it.
Example: =COUNTIF(A2:A5,"?????es") counts cells that have exactly 7 characters and end with "es"; in Microsoft's example the result is 2.
Watch out: common mistakes
- Upper and lower case don't matter. "apples" and "APPLES" match the same cells.
- Text longer than 255 characters. COUNTIF returns incorrect results when matching strings longer than 255 characters. In such cases Microsoft suggests splitting the text into parts with the & operator or the CONCATENATE function.
- The condition goes in quotes. Conditions such as ">55" or "apples" are written in quotes, while a cell reference (A4) is written without quotes.
Summary
- COUNTIF counts the number of cells that meet a condition: =COUNTIF(range, criteria).
- The condition can be a number, a comparison (">55"), a cell reference or text.
- For several conditions, use COUNTIFS or the sum of several COUNTIFs.
- The ? and * wildcards make searches more flexible; case doesn't matter.
If you want to learn Excel systematically for work, write to Estavor Akademiya: +994 77 754 10 12.
📚 Sources
🌐 The original article is in Azerbaijani; the official sources are available in Azerbaijani.
More articles
What is the difference between B1 and B2? Understand your English level
What do B1 and B2 levels mean, how do they differ and where do they sit on the CEFR scale? A simple explanation based on the Council of Europe's official descriptors.
What Is TORFL (TRKI)? Russian Language Exam Levels and Sections
What is TORFL (TRKI)? The 6 levels of the state Russian language test, A1–C2 equivalence, 5 subtests and the certificate — based on official test centres.
What is IELTS? Academic vs General Training, sections and scores
What is IELTS, Academic vs General Training, the duration of the 4 sections, the 0–9 band scale and how the overall score is calculated — based on ielts.org.