Excel: Use COUNTIF with Multiple Criteria in Same Column


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

Leave a Reply

Your email address will not be published.