Summary
The Excel COUNTIF function counts the number of cells in a range that meet a single specified condition. It is useful for counting occurrences of a certain value, finding how many cells contain numbers greater or less than a specific value, and similar tasks.
Syntax
=COUNTIF(range, criteria)
- range: The group of cells you want to look through.
- criteria: The condition that cells must meet to be counted. This can be a specific number, text, an expression, or a reference to a cell with the condition.
Return value
The count of cells that match the given condition.
How to use
Choose the range of cells to check and the condition that cells must match to be included in the count. The condition for matching can be text for exact matches or use quotation marks with symbols for comparing numbers.
Examples
Simple COUNTIF
Counting Cells with a Specific Number: Finding how many times a particular number shows up.
=COUNTIF(A1:A10, 5)
If A1:A10 has three cells with the number 5, COUNTIF gives back 3, showing that 5 appears three times.
COUNTIF for Specific Text
Counting How Many Times a Name Appears: Checking the frequency of a certain name in a list.
=COUNTIF(B1:B20, "Project X")
If “Project X” is found 4 times in B1:B20, this formula shows 4, indicating the name appears four times.
COUNTIF for Numbers Greater Than
Finding Cells with Numbers Above a Certain Value: Counting sales numbers that are over a goal.
=COUNTIF(C1:C30, ">500")
If there are 10 cells in C1:C30 with values over 500, COUNTIF will say 10, pointing out how many sales were above the goal.
COUNTIF with Wildcards
Looking for Cells that Start with Certain Letters: Counting entries that begin with “Pro”.
=COUNTIF(A1:A10, "Pro*")
If there are two cells in A1:A10 that start with “Pro”, like “Product” and “Profile”, COUNTIF returns 2, using wildcards to find text patterns.
Additional Notes
- COUNTIF doesn’t care about uppercase or lowercase letters, seeing “apple” and “Apple” as the same.
- For counting cells that need to meet more than one condition at the same time, consider using the COUNTIFS function, which can handle multiple criteria.