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. If you want to learn how to use the COUNTIF function for partial match, check out this guide.
Sometimes you might experience problems with COUNTIFS not working as expected in your file, especially when analyzing a list of data, and it returns an incorrect output such as a #VALUE! error, 0, a message indicating that there is an error in the formula, or some other unexpected value.
Some of the problems that can affect the output of the function 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.
Introduction to COUNTIFS
The COUNTIFS function in Excel is a versatile and powerful tool designed to help users count the number of cells that meet multiple criteria across one or more ranges. Unlike the COUNTIF function, which is limited to a single condition, the COUNTIFS function allows you to specify several criteria, making it ideal for more complex data analysis tasks.
For example, if you have a dataset with columns for product color, supplier, and quantity, you can use the COUNTIFS function to count how many rows meet all your specified conditions—such as counting the number of red products supplied by Microsoft with a quantity greater than 10. This ability to apply multiple criteria at once makes the COUNTIFS function especially useful for filtering and summarizing large sets of data.
The criteria you use with the COUNTIFS function can be based on textual criteria (like specific words or phrases), numbers, or even dates. For instance, you might want to count the number of cells in a column that contain the text “Completed,” or count how many entries fall within a certain date range. To do this, you simply specify the range of cells to be counted and the criteria that each cell must meet.
Here’s a basic example:
=COUNTIFS(A2:A10, "Red", B2:B10, ">10")
This formula will count the number of rows where column A contains “Red” and column B contains a value greater than 10.
By using the COUNTIFS function, you can quickly and accurately extract meaningful insights from your data, ensuring that only the cells that meet all your specified criteria are counted. Whether you’re working with sales data, inventory lists, or survey results, the COUNTIFS function is an essential tool for anyone looking to analyze data efficiently in Excel.
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 this case, the closed workbook is a file that needs to be opened for the formula to work.
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 first click 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. The logical operator and cell reference must be concatenated to form the correct criteria for the formula to work properly.
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. The logical operator and cell reference are concatenated to create the correct criteria for the COUNTIFS function.
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 count 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. Also, make sure that the cells containing numerical criteria are formatted as numbers, not as text, to avoid issues 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:
Also, make sure the cells you are counting are formatted as ‘Number’ or ‘General’ and not as ‘Text’, as incorrect formatting can cause the COUNTIF function to return inaccurate results.
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 from our list 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 because the criteria are no longer met.
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 find 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. This incorrect syntax leads to an unexpected result, as the function cannot return the correct output.
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.
The corrected formula worked and returned the expected result.
Alternatives and Best Practices
To get the most accurate results from the COUNTIFS function, it’s important to follow a few best practices and be aware of alternative approaches for different situations. One of the most common issues users face is forgetting to enclose textual criteria in double quotes, which can cause the formula to return an error or an incorrect count. Always make sure that any text you use as a criterion is surrounded by double quotes, for example:
``` "Red"
Another frequent mistake is using logical operators (such as “`>, “` <, or “`==) without properly concatenating them to a cell reference. When you want to compare values in your data to a value in another cell, use the ampersand (“` &) to join the operator and the cell reference, like this:
``` ">" & A1. This ensures that Excel interprets your criteria correctly.
It’s also crucial to ensure that all criteria ranges in your COUNTIFS formula are the same shape—that is, they have the same number of rows and columns. If the ranges are mismatched, Excel will return an error and the formula won’t work as expected.
Here are some best practices to follow when using the COUNTIFS function:
- Always enclose textual criteria in double quotes
``` "Completed"- Concatenate logical operators and cell references** using the ampersand;
```&
or
``` ">" & B2- Ensure all criteria ranges are the same size and shape** to avoid errors.
- Double-check your formula syntax** to make sure all commas, parentheses, and quotes are correctly placed.
If you only need to count cells based on a single criterion, the COUNTIF function is a simpler alternative. For example;
```=COUNTIF(A2:A10, "Red")will count all cells in the range A2:A10 that contain the text “Red”.
For more advanced scenarios, you might consider using the SUMIFS function, which works similarly to COUNTIFS but returns the sum of values that meet multiple criteria instead of just the count. Another powerful alternative is the FILTER function, which allows you to extract and work with data that meets multiple criteria, giving you even more flexibility in your analysis.
By following these best practices and choosing the right function for your needs, you can ensure that your formulas return the correct count and help you avoid common errors. Whether you’re working with one column or multiple columns, using the COUNTIFS function correctly will help you get the most out of your Excel data analysis.
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.