Excel is a powerful tool for working with data, and one of the most common tasks is to count how many cells meet certain criteria. For example, you might want to know how many products have a high rating and a low price, or how many employees have a certain skill and a certain salary range.
In this article, we will show you how to use the COUNTIFS function to count values that meet two or more conditions in Excel. We will also explain the basic theory behind the function, the procedures to follow, and some alternative approaches.
What is the COUNTIFS Function?
The COUNTIFS function is a built-in function in Excel that counts the number of cells in one or more ranges that meet one or more criteria. The syntax of the function is:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
where:
- criteria_range1 is the first range of cells to check against the first criterion.
- criteria1 is the first criterion that defines which cells to count. It can be a number, a text, a cell reference, an expression, or another function.
- criteria_range2, criteria2, … are optional additional ranges and criteria that you can specify up to 127 pairs.
The COUNTIFS function applies the AND logic, which means that all the criteria must be met in order to count a cell. For example, if you use the formula:
=COUNTIFS(A2:A10, “red”, B2:B10, “>10”)
it will count the number of cells in the range A2:A10 that are “red” AND the corresponding cells in the range B2:B10 that are greater than 10.
How to Use the COUNTIFS Function with Multiple Criteria
To use the COUNTIFS function with multiple criteria, you need to follow these steps:
- Identify the ranges and criteria that you want to use for counting. Make sure that the ranges have the same size and shape, and that the criteria are valid and consistent.
- Enter the COUNTIFS function in a cell where you want to display the result. Start with an equal sign (=), followed by the function name and an opening parenthesis.
- Enter the first range and criterion pair, separated by a comma. For example, if you want to count the cells in the range A2:A10 that are “red”, enter A2:A10, “red”.
- Enter any additional range and criterion pairs, separated by commas. For example, if you also want to count the cells that are greater than 10 in the range B2:B10, enter B2:B10, “>10”.
- Close the parenthesis and press Enter. The function will return the count of cells that meet all the criteria.
Example: Counting Products by Rating and Price
Let’s say you have a table of products with their ratings and prices, and you want to count how many products have a rating of 4 or 5 stars and a price below $50. Here is how you can use the COUNTIFS function to do that:
- Select a cell where you want to display the result, such as C12.
- Enter the formula: =COUNTIFS(C2:C10, “>=4”, D2:D10, “<50”)
- Press Enter. The function will return 3, which is the number of products that meet the criteria.
| Product | Rating | Price |
|---|---|---|
| A | 3 | 40 |
| B | 4 | 30 |
| C | 5 | 20 |
| D | 2 | 60 |
| E | 4 | 50 |
| F | 5 | 45 |
| G | 3 | 55 |
| H | 4 | 25 |
| I | 1 | 70 |
| Result | 3 |
Alternative Approaches
The COUNTIFS function is not the only way to count values that meet multiple criteria in Excel. Here are some other approaches that you can use:
- Use the SUMPRODUCT function with logical expressions. The SUMPRODUCT function multiplies corresponding elements in one or more arrays and returns the sum of the products. You can use logical expressions to create arrays of 1s and 0s that indicate whether the criteria are met or not. For example, the formula:
=SUMPRODUCT((C2:C10>=4)*(D2:D10<50))
will return the same result as the COUNTIFS function in the previous example.
- Use the COUNTIF function with an array constant. The COUNTIF function counts the number of cells in a range that meet a single criterion. You can use an array constant to specify multiple criteria and then sum the results. For example, the formula:
=SUM(COUNTIF(C2:C10,{4,5}))
will count the number of cells in the range C2:C10 that are either 4 or 5. Note that this approach only works for OR logic, not AND logic.
- Use a pivot table. A pivot table is a tool that allows you to summarize and analyze data in a table format. You can use a pivot table to count values that meet multiple criteria by adding the fields that you want to use as criteria to the Filters area and the Values area. For example, you can create a pivot table like this to count the products by rating and price:
| Rating | Price | Count of Product |
|---|---|---|
| 4 | <50 | 2 |
| 5 | <50 | 1 |
| Grand Total | 3 |
