I need to count cells which meet 2 sets of criteria and am trying to use the countifs function.

Range 1, Criteria referenced in cell Z9 (eg. Cracked cell)

Range 2, Criteria referenced in cell AA8 (eg. Minor)

This is the formula I am using which seems to be giving me a "#VALUE!" error.


Please help rectifying.

  • Your are referring to different number of rows in the two criteria, that won't work with COUNTIFS, use the same number of cells in both references. – Máté Juhász Aug 10 '16 at 4:00
  • 1
    I cant believe it was that silly a mistake. Thank you! Its been resolved – Shantanu Aug 10 '16 at 4:12
  • Welcome to Super User and glad you solved this. People may see the question and come to help or may have a similar problem and come looking for a solution. The solution is buried in comments, which aren't permanent. It would be helpful to others if you post a brief answer with the solution. In two days, you'll be able to accept the answer by clicking the checkmark next to it, which will indicate the problem is solved. – fixer1234 Aug 10 '16 at 4:21

Answer posted by Mate Juhasz.

COUNTIFS only works for ranges with the same number of cells. As I had different sized ranges in my formula, I was getting an error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.