How to Use COUNTIF with OR in Excel

by Tutor Aspire January 17, 2023

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.

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:

COUNTIF with OR in Excel

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.

The following tutorials explain how to perform other common COUNTIF() operations in Excel: