Search code examples
excelexcel-formulaexcel-2013

COUNTIFS function not working with single cell range

I'm having an issue with COUNTIFS function in excel, i have a very large formula that I was getting an error on, but I have slowly taken the formula apart to a smaller subset and identified the issue I'm having. Excel won't use COUNTIFS correctly when the criteria range is one cell. Here's the formula I'm using:

=COUNTIFS(A7:A7,1,Sheet1!F:F,$A$9,Sheet2!S:S,$B$8)

Whenever I take the first criteria out "A7:A7,1" my formula returns a value, without it I receive #VALUE! error. Can you not use one cell as the criteria for CountIFS? I've also tried replacing "A7:A7" with just "A7" or "$A$7". The first criteria is the most important, because i'm going to be using a listbox for that cell that will change the values in my range for interactive graphs..

Solution

  • The ranges in the countifs need to be the same size.

    Wrap the COUNTIFS() in a IF:

    =IF(A7=1,COUNTIFS(Sheet1!F:F,$A$9,Sheet2!S:S,$B$8),0)