The COUNTIF function in Excel allows you to count the number of cells that meet a specific condition. For example, you can use COUNTIF to count how many products are in a certain category, how many students scored above a certain grade, or how many sales are above a certain amount.
However, sometimes you may want to count cells based on more than one condition, or based on a range of values as criteria. For example, you may want to count how many products are in category A or B, how many students scored between 80 and 90, or how many sales are between $1000 and $2000.
In this article, we will show you how to use WorksheetFunction.CountIf with a range of values as criteria in Excel formula. We will explain the basic theory, the procedures, and give a detailed example with real numbers. We will also show you some other approaches that you can use to achieve the same result.
Basic Theory
The basic syntax of the COUNTIF function is:
=COUNTIF(range, criteria)
where range is the range of cells to count, and criteria is the condition that defines which cells to count. The criteria can be a number, text, expression, or cell reference.
For example, the formula:
=COUNTIF(A2:A10, “red”)
will count the number of cells in the range A2:A10 that contain the text “red”.
However, if you want to use a range of values as criteria, you cannot simply enter the range as the criteria argument. For example, the formula:
=COUNTIF(A2:A10, B2:B10)
will not count the number of cells in A2:A10 that match the values in B2:B10. Instead, it will only compare the first cell in each range, A2 and B2, and return the count of cells in A2:A10 that are equal to B2.
To use a range of values as criteria, you need to use an array formula. An array formula is a formula that performs calculations on multiple values at once, and returns an array of results. To enter an array formula, you need to press Ctrl + Shift + Enter instead of just Enter.
For example, the formula:
=COUNTIF(A2:A10, B2:B10)
entered as an array formula, will count the number of cells in A2:A10 that match any of the values in B2:B10. The formula will compare each cell in A2:A10 with each cell in B2:B10, and return an array of counts, such as {1;0;0;2;1;0;0;1;0}. Then, the formula will sum up the array and return the total count, which is 5 in this case.
Procedures
To use WorksheetFunction.CountIf with a range of values as criteria in Excel formula, follow these steps:
- Select a cell where you want to enter the formula.
- Type =COUNTIF( and select the range of cells to count, such as A2:A10.
- Type a comma, and then select the range of values to use as criteria, such as B2:B10.
- Press Ctrl + Shift + Enter to enter the formula as an array formula. You should see curly braces around the formula, indicating that it is an array formula.
- The formula will return the count of cells that match any of the values in the criteria range.
Example
Let’s say you have a table of sales data, as shown below:
| Product | Category | Price |
|---|---|---|
| A | X | 1200 |
| B | Y | 800 |
| C | Z | 600 |
| D | X | 1500 |
| E | Y | 900 |
| F | Z | 700 |
| G | X | 1300 |
| H | Y | 1000 |
| I | Z | 500 |
You want to count how many products have a price between $800 and $1200, inclusive. To do this, you can use WorksheetFunction.CountIf with a range of values as criteria in Excel formula.
First, you need to create a range of values that represent the lower and upper bounds of the criteria. For example, you can enter 800 in cell D2 and 1200 in cell D3, as shown below:
| Product | Category | Price | Criteria |
|---|---|---|---|
| A | X | 1200 | 800 |
| B | Y | 800 | 1200 |
| C | Z | 600 | |
| D | X | 1500 | |
| E | Y | 900 | |
| F | Z | 700 | |
| G | X | 1300 | |
| H | Y | 1000 | |
| I | Z | 500 |
Then, you can enter the following formula in cell E2:
=COUNTIF(C2:C10, D2:D3)
and press Ctrl + Shift + Enter to enter it as an array formula. The formula will count the number of cells in C2:C10 that are equal to or between the values in D2:D3. The result is 4, as shown below:
| Product | Category | Price | Criteria | Count |
|---|---|---|---|---|
| A | X | 1200 | 800 | 4 |
| B | Y | 800 | 1200 | |
| C | Z | 600 | ||
| D | X | 1500 | ||
| E | Y | 900 | ||
| F | Z | 700 | ||
| G | X | 1300 | ||
| H | Y | 1000 | ||
| I | Z | 500 |
Other Approaches
There are some other ways to count cells based on a range of values as criteria in Excel formula. Here are some examples:
- You can use the COUNTIFS function, which allows you to specify multiple criteria with logical operators. For example, the formula:
=COUNTIFS(C2:C10, “>=800”, C2:C10, “<=1200”)
will count the number of cells in C2:C10 that are greater than or equal to 800 and less than or equal to 1200. The result is the same as using WorksheetFunction.CountIf with a range of values as criteria.
- You can use the SUMPRODUCT function, which multiplies corresponding elements in two or more arrays and returns the sum of the products. For example, the formula:
=SUMPRODUCT((C2:C10>=800)*(C2:C10<=1200))
will count the number of cells in C2:C10 that are greater than or equal to 800 and less than or equal to 1200. The formula works by creating two arrays of TRUE and FALSE values based on the criteria, and then multiplying them element-wise. The TRUE values are converted to 1 and the FALSE values are converted to 0. The result is an array of 1 and 0 values, which are then summed up to get the count.
- You can use the FREQUENCY function, which returns a frequency distribution of values in a range. For example, the formula:
=SUM(FREQUENCY(C2:C10, D2:D3))
will count the number of cells in C2:C10 that are equal to or between the values in D2:D3. The formula works by creating an array of frequency counts based on the bins in D2:D3, and then summing up the array to get the total count.
