Often you may want to count the number of cells in a range in Excel that meet one of several criteria.
You can use the following basic syntax to do so:
=SUM(COUNTIF(A:A,{"Value1", "Value2", "Value3"}))
This particular formula counts the number of cells in column A that are equal to “Value1”, “Value2”, or “Value3.”
The following example shows how to use this syntax in practice.
Example: Use COUNTIF with OR in Excel
Suppose we have the following dataset in Excel that shows information about various basketball teams:
We can use the following formula to count the number of cells in column A that have a value of “East” or “South”:
=SUM(COUNTIF(A:A,{"East", "South"}))
The following screenshot shows how to use this formula in practice:
We can see that a total of 5 cells have a value of “East” or “South” in column A.
Note that we can also use similar syntax to count the number of cells that have specific numeric values.
For example, we can use the following formula to count the number of cells in column C that have a value of 95, 99, or 103:
=SUM(COUNTIF(C:C,{95, 99, 103}))
The following screenshot shows how to use this formula in practice:
We can see that a total of 6 cells have a value of 95, 99, or 103 in column C.
We could also use similar syntax to count the number of cells that have values greater than or less than certain numbers.
For example, we can use the following formula to count the number of cells in column C that have a value greater than 100 or less than 90:
=SUM(COUNTIF(C:C,{95, 99, 103}))
The following screenshot shows how to use this formula in practice:
We can see that a total of 7 cells have a value greater than 100 or less than 90 in column C.
Additional Resources
The following tutorials explain how to perform other common COUNTIF() operations in Excel:
How to Use COUNTIF From Another Sheet in Excel
How to Use COUNTIFS with a Date Range in Excel