Table of Contents
The COUNTIF function in Excel can be used with an OR statement to count the number of cells in a range that meet one or more criteria. The OR statement is written as an array with each criteria separated by a comma. The COUNTIF function can then be used to count the number of cells in the specified range that meet at least one of the criteria. This is a useful tool to quickly count the number of cells meeting certain criteria in a large data set.
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,{">100", "<90"}))
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.
The following tutorials explain how to perform other common COUNTIF() operations in Excel: