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.