This caught me out the other day: I was looking to do something that I at first pegged as a COUNTIFS() problem, only to realise that it did not work as imagined. The problem required a range to be evaluated for true for one value out of a list of values, in other words, count this record if one of the evaluations returns TRUE OR the other evaluation returns TRUE, not if one of the evaluations returns TRUE AND the other evaluation returns TRUE. Essentially we want to evaluate (“search”) one range for multiple values. I immediately thought of SQL statements that have a WHERE clause such as IN (value1, value2, value3).
Required knowledge:
1. Syntax
=SUM(COUNTIFS(range,{value1,value2,value3}))
Essentially, this is the way a SQL statement like SELECT * WHERE `field` IN (value1, value2, value3) works.
2. Example
2.1 Multiple COUNTIF()
We are going to summarise the student data, counting the number of students by grade. You have already worked with the COUNTIF() function which will allow us to count the number of students in any specific grade. If we want to count all the students in Grades 11 and 12, we could count the number of records where the grade is greater than 10. While this works as a solution, it really is a terrible idea as the assumption is that there will NEVER be a record where the grade is 13 or more, and we all know about the dangers of making assumptions! Example (in cell L6):
=COUNTIF($G$2:$G$460,">10")
But what if we want to count all the students in grades 10 and 11? We could solve it by using the result of one COUNTIF() function added to the result from a second COUNTIF() function like so (this formula in cell L7):
=COUNTIF($G$2:$G$460,10)+COUNTIF($G$2:$G$460,11)
2.2 A better way
The above solution is going to get very clunky if you have many many different grades (this formula is cell L8):
=SUM(COUNTIF($G$2:$G$460,{10,11}))
In cell L10:
=SUM(COUNTIF($H$2:$H$460,{"Alpha","Charlie"}))
References:
- (No date) SQL WHERE Clause. Available at: https://www.w3schools.com/sql/sql_where.asp. (Accessed: 15 March 2024).
- Count cells equal to this or that (no date) Exceljet. Available at: https://exceljet.net/formulas/count-cells-equal-to-this-or-that (Accessed: 9 February 2024).