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