Md. Shamim Reza
This article highlights the possible reasons behind and solutions for the COUNTIFS function in Excel not working with multiple criteria. If you need to count values within a dataset based on multiple criteria, using the COUNTIFS function is probably the best way to do that. Everything has its limitations and so does the Excel COUNTIFS function. Sometimes it may not work as required. In this article, we will discuss the possible reasons behind this and try to find solutions for them.
Download Practice Workbook
You can download the practice workbooks from the download button below.
Possible Reasons Why Excel COUNTIFS Function Is Not Working with Multiple Criteria
Here are the possible reasons for the Excel COUNTIFS function not working with multiple criteria.
- Criteria without Double-quotes: If you use texts for multiple criteria but do not put them in double-quotes then the COUNTIFS formula won’t work.
- Dissimilar Criteria_range: If you use multiple criteria but the Criteria_range arguments do not have the same number of rows and columns, the COUNTIFS function won’t work.
- Cell Reference in Double-quotes: If you use cell references as criteria but put them in double-quotes then Excel will interpret them as simple texts. Therefore the COUNTIFS formula will not be working as you expect.
- Multiple OR Criteria: You can’t use the same range again as criteria_range arguments for multiple criteria. Otherwise, the COUNTIFS function will not work.
- Not Using Proper Wildcards: The COUNTIFS function will not work if you use partially matching texts as criteria but do not put proper wildcards with them.
- Reference from Closed Workbook: The COUNTIFS formula does not work if it contains references from a workbook that is not open.
2 Suitable Solutions If COUNTIFS Is Not Working with Multiple Criteria in Excel
We can say that the possible reasons discussed above are some of the limitations of the COUNTIFS function by design. You must keep those points in mind while using this function. But there are ways around 2 of the above reasons to make the function work. We are going to highlight them below using the following dataset.
Case 1: COUNTIFS Not Working with Multiple OR Criteria
The COUNTIFS function returns 0 if you use the same range for multiple criteria as shown in the following picture.
=COUNTIFS(B5:B16,G5,B5:B16,G6)
- There are 2 different ways around this issue. The first is to apply the COUNTIFS formula for each of the criteria separately and add the counts returned by them.
=COUNTIFS(B5:B16,G5)+COUNTIFS(B5:B16,G6)
- Obviously, that may not be convenient and so we will see the other alternative. So we will combine the SUM function with this formula to get the desired result as follows. Although it seems more efficient, you can only use text array (i.e. you need to put each of the criteria texts inside double quotes, separate them by commas or semicolons, and then enclose all of them with curly brackets) as criteria.
=SUM(COUNTIFS(B5:B16,{"Ellen","Caroline"}))
Case 2: COUNTIFS Not Working with Multiple Criteria from Closed Workbook
You can use external references from a currently open workbook with the COUNTIFS function. But if you close the source workbook and update any reference, the formula will return #VALUE!
- Assume you have the following dataset in the location D:\Source.xlsx.
- Now open the source workbook and enter the following formula in cell C6. You will see the formula working fine.
=COUNTIFS([Source.xlsx]Sheet1!$B$5:$B$16,C4,[Source.xlsx]Sheet1!$C$5:$C$16,C5)
- Next, apply the following formula using the SUMPRODUCT function in cell C7 for the same result.
=SUMPRODUCT(([Source.xlsx]Sheet1!$B$5:$B$16=C4)*([Source.xlsx]Sheet1!$C$5:$C$16=C5))
- Now close the source workbook and change the criteria values. After that, you will see the COUNTIFS formula returning an error while the SUMPRODUCT formula working just fine. So using the SUMPRODUCT function instead of the COUNTIFS function might be more effective in this case.
Quick Notes
- Don’t forget to put the criteria texts inside double quotes.
- The criteria_range argument must have the same number of rows and columns.
- You must not put any quotes around cell references.
- Don’t use the same range again as the criteria_range for other criteria.
- Don’t forget to use proper wildcards if you use criteria texts in such a way that they may not exactly match the values of the dataset.
- You must not use references from a closed workbook. Use the SUMPRODUCT function instead if necessary.
Conclusion
Now you know the reasons behind and solution for COUNTIFS not working for multiple criteria in excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDmey blog to explore more about excel. Stay with us and keep learning.
