Having trouble with counting two criteria with countif

Question

I have two criterias in excel.

I was counting anything that is 2014 and grade k-8 using countifs: =COUNTIFS(A2:C3,"2014",D2:E3,"Grades K-8") but it seems to fail.

Year 1  Year 2  Year 3  Grades K-8  Grades 9-12 Teachers 
2014    2015            Grade K-8   Grade 9-12  Teachers
2016    2017    2018    Grade K-8   Grade 9-12  Teachers

I keep getting value error. Any help is much appreciated!

Answer 1

This should do the trick :)

In cell H3, which search for the criteria Grade 9-12 and 2014

=SUMPRODUCT((E2:E6="Grade 9-12")*(A2:C6=2014))

The SUMPRODUCT looks for all combinations given the two ranges and gives you how many times they occur.

Since you search row wise for one criteria and column wise for another you can't use countifs. Or more precisely, your are looking in a range that across both multiple rows and columns when you search for year. So you want countif to compare each item in that array against the item of another array.