How to use COUNTIF in Excel
Excel is widely known for its ability to manage and analyse data efficiently. It has many built in functions, of which “COUNTIF” is a very powerful one to use.
COUNTIF allows you to count cells that meet a specific condition without having to filter or manually check the data and is very simply to use and understand.
Syntax of COUNTIF
The syntax of the COUNTIF function is straightforward:
=COUNTIF(range, criteria)
- range: The group of cells you want to evaluate.
- criteria: The condition that determines which cells should be counted.
The criteria argument can be a number, text, date, expression, or even a reference to another cell.
Examples of COUNTIF
1. Counting Numbers
If we had a list of sales per day in cells A1:A10 and want to know how many days we have more than 80 sales, we can write:
=COUNTIF(A1:A10, ">=80")
This formula will return the number of days with sales greater than or equal to 80.
2. Counting Text
If you are tracking a survey response and want to know how many “Good” response there were, the formula is (assuming the response in stored in cells B1:B50):
=COUNTIF(B1:B50, "Good")
3. Using Wildcards
COUNTIF also works with wildcards. For example:
o ? represents a single character.
o * represents any sequence of characters.
If you want to count all names in cells C1:C100 that begin with “A,” you can write:
=COUNTIF(C1:C100, "A*")
To count all words which have “B” as a second letter then use
=COUNTIF(C1:C100, "?B")
To find all words which start with “B” and have 2 more letters and end in “D” use
=COUNTIF(C1:C100, "B??*d")
In this example words like “Bird” and “Bread” will be counted as they start with “B” and have 2 more letters after this. Then they have 1 or more letters after these three and the last letter is “D”. This means words like “Bid” will not be counted as, even though it starts with “B” and has 2 more letters, there are no more letters after these 3 to check for the last letter being “d”.
4. Counting Dates
To count how many sales occurred after January 1, 2025, in column D, use:
=COUNTIF(D1:D200, ">1/1/2025")
Benefits of Using COUNTIF
1. Quick
COUNTIF eliminates the need to sort or filter data manually. A single formula can quickly provide totals that might otherwise require multiple steps.
2. Flexible
Because it accepts text, numbers, dates, and expressions, COUNTIF adapts to many scenarios.
Limitations of COUNTIF
While COUNTIF is powerful, it does have some limitations:
1. Single Condition Only
COUNTIF can only evaluate one condition at a time. For multiple conditions, you need COUNTIFS (the plural version introduced in later versions of Excel).
2. Non-Case-Sensitive
COUNTIF is not case-sensitive. For instance, “apple” and “Apple” are treated the same.
3. Criteria Flexibility
Writing criteria requires attention to detail. For example, when using operators like >, <, or =, they must be included inside quotation marks and using wildcards might produce results you don’t expect if you are not careful.