Excel COUNTIFS Not Working with Multiple Criteria (2 Solutions)

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.

dataset to apply COUNTIFS


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)

COUNTIFS not working with multiple criteria

  • 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)

COUNTIFS not working issue fixed

  • 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"}))

COUNTIFS with multiple criteria


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.

source workbook

  • 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)

COUNTIFS working when source workbook is open

  • 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))

SUMPRODUCT as an alternative to COUNTIFS

  • 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.

COUNTIFS not working with closed workbook


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.

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo