The Excel COUNTIFS function is used to get the number of cells in a range or ranges that meet a specified set of conditions or criteria.
Sometimes you might experience countifs not working as expected and it returns a #VALUE! error, 0, amessage indicating that there is an error in the formula or some other unexpected value.
Some of the reasons why the function doesn’t work as expected in Microsoft Excel are:
- When a formula refers to a range in a workbook that is closed.
- Using a value from another cell without concatenating it to the logical operator used.
- Not inserting textual criteria in double quotes (“ “)
- Subsequent ranges do not have the same number of columns and rows as the first criteria range.
- Not inserting both numerical criteria and logical operator in double quotes (“ “)
- Trying to get a count using the OR logic.
- Subsequent changes in dataset.
- Mistakes in the formula.
In this tutorial, we explain how these reasons can be resolved so that the function returns the correct count that we are looking for.
1. The formula refers to a range that is in another workbook that is closed
When the COUNTIFS function refers to a range that is in another workbook that is closed, it will return a #VALUE! error instead of the number of cells that we are expecting.
In the following example, instead of the formula returning the number of red products, a #VALUE! error has been generated because the formula refers to a range in another workbook called Product Colours that is closed.
Solution
The solution to countifs not working in this scenario is to open the closed workbook and then press F9 on the keyboard so that the formula recalculates the correct parameters. We will then get the correct count as shown in the following example.
2. Using value from another cell without concatenating the cell reference to the logical operator
In case you use a logical operator and cell reference as criteria for the COUNTIFS function in a formula, and you do not concatenate the cell reference to the logical operator by inserting & before the cell reference, the function will return a 0 instead of the expected count.
This is shown in the following example where the formula =COUNTIFS(G2:G6,”>M2″) has been used.
Solution
In the following example, we need to type in the correct formula =COUNTIFS(G2:G6,”>” &M2) in cell I3.When we press the ENTER key, we get the correct count of 4.
In the correct formula, only the logical operator of greater than (>) is in double quotes (“ “), and the concatenating operator & is placed before the cell reference M2.
3. Not putting double quotes (“ “) around textual criteria
If textual criteria are not inserted in double quotes, the COUNTIFS function will return a 0 value rather than the correct count that we expect as shown in the example below.
The formula in cell J2 is =COUNTIFS(C2:C6,Red). The formula returns 0 because the criteria Red was not put in double quotes (“ “).
Solution to countifs not working
We need to put the textual criteria in the formula in double quotes as shown in the following example and we will then get the correct count of 2.
4. The subsequent criteria range does not have the same shape as the first criteria range
When we use multiple criteria in the COUNTIFS function, and the additional optional criteria ranges do not match the first criteria range, the #VALUE error will be generated.
In the following example, the formula =COUNTIFS(B2:B13,”Red”,C2:C10,”Microsoft”) has been entered in cell G3 in an attempt to get the number of red products supplied by Microsoft.
When we press enter we get the #VALUE! error as shown below.
This is because the second criteria range of C2:C10 does not have the same number of cells as the first criteria range of B2:B13.
Solution
To correct this problem, we have to ensure that the second criteria range is the same size as the first criteria range as shown below:
When we now press the ENTER key, we will get the correct count of 2 as shown below:
5. Not inserting both numerical criteria and logical operator in double quotes (“ “)
When we use logical operators such as equals to (=), greater than (>), Less than (<), and not equals to (<>) in a formula and fail to include both the numerical criteria and the logical operator in the same double quotes, Excel will give us a message that there is an error with the formula.
In the following example, we did not include both the logical operator and the numerical criteria in the same double quotes. Only the logical operator is inside double quotes.
When we press ENTER key, we get the following message:
Solution
We need to ensure that we include both the logical operator and the numerical criteria inside the same double quotes as shown below:
When we now press ENTER key we will get the correct count of 5 as shown below:
6. Trying to get a count using the OR logic
Although the COUNTIFS function can generate correct counts using the AND logic, it only generates a 0 value when we try to use it to compute using the OR logic.
In the following example, we are trying to get the number of red and blue product categories using the OR logic.
When we press the ENTER key, we get the unexpected value of 0 as shown below. This is because the COUNTIFS function cannot calculate using the OR logic.
Solution
We can get around this OR logic problem by using the COUNTIFS and SUM functions together.
In the following example, we have entered the formula =SUM(COUNTIFS(B2:B13,{“Red”,”Blue”})) in cell G3.
When we press the ENTER key, we get the correct value of 6 as shown below. This is because the COUNTIFS function returned 3 counts of Red and 3 counts of Blue from the criteria range B2:B13, and the SUM function summed up the values to get 6.
7. Subsequent changes in the dataset
Changes in a dataset can make the COUNTIFS function not generate expected values. For example, in the following dataset, the COUNTIF function is working properly returning the correct count of Blue products.
But if there are any changes in the data, for example in this case we add the word colour to Blue, the COUNTIFS function now shows the incorrect value of 0.
Solution
This situation can be remedied by using wildcard characters with the COUNTIF function.
In the following example, we have used the asterisk (*) character in the criteria, and now we can get the correct count of 4 products. This is because the COUNTIF function can now look for partial matches in the criteria range.
8. Mistakes in formula
Sometimes the COUNTIF function will not work because there is an error in the formula, for example forgetting to put a comma separator between the criteria range and the logical operator.
In the following example, there is no comma between the criteria range D2:D13 and the logical operator greater than (>).
When we press the ENTER key, we get the message box informing us that there is an error in the formula.
Solution
We need to be extra careful when writing formulas to ensure that the syntax is correct. In the following example, inserting a comma between the criteria range and the greater than (>) operator solves the problem and we get the correct count of 5.
Conclusion
COUNTIFS is such a powerful function for generating the number of cells in data ranges that meet specified conditions.
In this tutorial, we have looked at different reasons why this function sometimes does not work as expected and ways of resolving them.