How to Use COUNTIF with OR in Google Sheets


Often you may want to count the number of cells in a range in Google Sheets that meet one of several criteria.

You can use the following basic syntax to do so:

=ArrayFormula(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 Google Sheets

Suppose we have the following data in Google Sheets:

We can use the following formula to count the number of cells in column A that have a value of “East” or “South”:

=ArrayFormula(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. 

We can also use the following formula to count the number of cells in column C that have a value of 95, 99, or 103:

=ArrayFormula(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.

Additional Resources

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

Google Sheets: How to Use COUNTIF From Another Sheet
Google Sheets: How to Use COUNTIF with Multiple Ranges
Google Sheets: How to Use COUNTIFS with a Date Range

Leave a Reply

Your email address will not be published.