Excel COUNTIFS function

Summary

The Excel COUNTIFS function counts the number of cells in a range that meet multiple criteria. It’s great for when you need to count cells that match several conditions at once, like finding the number of sales in a particular region that exceed a certain amount.
Syntax
				
					=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
				
			
  • criteria_range1: The first range of cells to evaluate with the first condition.
  • criteria1: The condition that cells in the first range must meet.
  • [criteria_range2, criteria2], …: Additional ranges and their corresponding conditions. You can specify up to 127 pairs.
Return value
The number of cells that meet all the given conditions.

How to use

Input the ranges and their corresponding conditions. Each pair of range and condition adds a new layer of criteria, narrowing down the cells that are counted based on all the specified conditions.

Examples

Simple COUNTIFS
Counting Cells with Exact Numbers and Conditions: Finding entries within a specific number range.
				
					=COUNTIFS(A1:A10, ">5", A1:A10, "<10")
				
			
If A1:A10 has four cells with numbers between 6 and 9, COUNTIFS reports 4, showing how many numbers fall into this range.
COUNTIFS for Different Criteria
Counting Based on Multiple Conditions Across Columns: Calculating the number of products with sales above a certain level in a specific region.
				
					=COUNTIFS(B1:B20, ">500", C1:C20, "East")
				
			
If B1:B20 represents sales figures and C1:C20 regions, and there are 5 sales over $500 in the “East” region, COUNTIFS returns 5.
COUNTIFS with Date Range
Finding Entries Within a Specific Date Range: Counting sales in a particular month.
				
					=COUNTIFS(D1:D30, ">=01/01/2022", D1:D30, "<=01/31/2022")
				
			
This counts how many entries in D1:D30 fall within January 2022. If there are 7, COUNTIFS shows 7.
COUNTIFS for Text and Numbers
Combining Text and Number Criteria: Counting how many times a specific project received a certain score.
				
					=COUNTIFS(A1:A10, "Project X", B1:B10, ">=80")
				
			
If A1:A10 lists project names and B1:B10 contains scores, and “Project X” has 3 scores of 80 or more, COUNTIFS reports 3.

Additional Notes

  • COUNTIFS is case-insensitive for text criteria.
  • It allows for detailed data analysis by applying multiple filters to your data set.
  • Ensure the ranges you specify are the same size to avoid errors.

Related Functions

Excel COUNTA function

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

Excel COUNT function

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

Excel COUNTBLANK function

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

Excel COUNTIF function

The Excel COUNTIF function counts cells that meet a single condition, vital for targeted data quantification.

Content Navigation