Skip to content

COUNTIF With OR Criteria

In this article, I will show you how to use COUNTIF to count values in a range that meets one of several possible criteria.

In other words, you will learn how to count cells that contain this, that, or something else. I will show you how to do this in Excel and Google Sheets.

The Formula for Excel

Take the following example. The goal is to count the cells that contain either red, blue, or green.

Use the following formula.

=SUM(COUNTIF(A2:A10, {"Red", "Blue", "Green"}))

Formula Explanation

Notice that the second argument of the COUNTIF function is an array. The array signals the COUNTIF function to count cells for each listed value.

The COUNTIF function counts the cells that contain the first item in the array. It then counts the cells containing the second item. And finally, it counts the cells containing the third item.

The COUNTIF function results in an array of three numbers.

=SUM( {3, 2, 2} )

This array passes to the SUM function, which adds the numbers to give you the final count.

The Formula for Google Sheets

The formula for Google Sheets is similar to the Excel version; however, there is a small difference.

Let’s look at the same example as before. The goal is to count the cells that contain either red, blue, or green.

This time, the formula looks like this.

=SUM(ARRAYFORMULA(COUNTIF(A2:A10, {"Red", "Blue", "Green"})))

Notice that you have to include the ARRAYFORMULA function this time.

Formula Explanation

Once again, the second argument of the COUNTIF function is an array. The array signals the COUNTIF function to count cells for each listed value.

Also, since the COUNTIF function includes an array, you must wrap it in the ARRAYFORMULA function to get it to work properly.

The COUNTIF function then counts the cells that contain the first item in the array. It then counts the cells containing the second item. And finally, it counts the cells containing the third item.

The COUNTIF and ARRAYFORMULA function combination results in an array of three numbers.

=SUM( {3, 2, 2} )

This array passes to the SUM function, which adds the numbers to give you the final count.

And that is how to use COUNTIF with OR criteria in Excel and Google Sheets.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top