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!
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.
Based on the layout of your data and that you are only looking in 1 column for the grade, and the years you are looking for is limited to only 3 columns you can use the following formula:
=COUNTIFS(A:A,2018,D:D,"Grade K-8")+COUNTIFS(B:B,2018,D:D,"Grade K-8")+COUNTIFS(C:C,2018,D:D,"Grade K-8")
Basically the check inside the COUNTIFS, the multiple criteria are all AND checks. SInce the mutiple columns for each year column is an OR, you need to check each year column individually. You can use + like an OR operations and repeat the columns checks.
If you need to check multiple columns and combinations of columns the formula will get unruly and you are better off with an array type formula like Wizhi has for an answer.
