Excel COUNTIF function

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.

Related Functions

Excel COUNTA function

The Excel COUNTA function counts non-empty cells, crucial for assessing the extent of data in a range.

Excel COUNTIFS function

The Excel COUNTIFS function counts cells meeting multiple criteria, essential for complex data categorization.

Excel COUNTBLANK function

The Excel COUNTBLANK function counts empty cells in a specified range, useful for identifying gaps in data sets.

Excel COUNT function

The Excel COUNT function counts cells containing numbers, fundamental for quantifying data entries in a range.

Content Navigation