You can use the following syntax to perform a COUNTIF function with multiple criteria in the same column in Excel:
=SUMPRODUCT(COUNTIF(A2:A16,{"word1","word","word3"}))
This particular formula will return the count of cells in the range A2:A16 where the value is equal to “word1”, “word2”, or “word3.”
The following example shows how to use this syntax in practice.
Example: Use COUNTIF with Multiple Criteria in Same Column
Suppose we have the following dataset in Excel that contains the names of various basketball teams:
We can use the following formula to count the number of cells in the range A2:A16 that are equal to “Mavs”, “Celtics”, or “Spurs”:
=SUMPRODUCT(COUNTIF(A2:A16,{"Mavs","Celtics","Spurs"}))
The following screenshot shows how to use this formula in practice:
We can see that a total of 9 cells were equal to one of these three values.
We can manually count each of these 9 cells to verify that this is correct:
There are indeed 9 cells that are equal to “Mavs”, “Celtics”, or “Spurs.”
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Delete Rows with Specific Text
Excel: How to Check if Cell Contains Partial Text
Excel: How to Check if Cell Contains Text from List
