Count With OR Logic in Google Sheets

In this tutorial, we count cells based on several conditions. Any of these conditions can be true to count the cell. We will present three solutions below. Pick the one that works best for your spreadsheet.

Make a copy of this Google Sheet with the examples to follow along.

Video Tutorial

Video thumbnail
YouTube Video about Counting with OR Logic

Adding Multiple COUNTIF Statements

First, the easiest method to count a list with multiple OR conditions is to SUM a series of COUNTIF functions. We want to count the cells that contain Bread, Apples, or Milk.

Several COUNTIF statements added together
Several COUNTIF Functions

ℹ️ You can insert line breaks in your formula by holding down alt and pressing enter.

=COUNTIF(A2:A7,"Bread")+COUNTIF(A2:A7,"Apples")+COUNTIF(A2:A7,"Milk")

This technique gets the job done, and it may be all you need. However, as you add more conditions, the formula can get long. Let’s look at an alternative that stays smaller as you add more conditions.

Count With an OR Condition With ARRAYFORMULA

The following technique involves a function called ARRAYFORMULA. ARRAYFORMULA repeats functions, repeating the SUM and COUNTIF functions.

Counting using an ARRAYFORMULA for OR condition
Counting with ARRAYFORMULA

=ArrayFormula(SUM(COUNTIF(A2:A7,{"Bread", "Apples", "Milk"})))

The {}s tell Google Sheets to expect an array; then, you need to separate the values inside of it by commas. However, the formula hard to read. Therefore, let’s correct that by using the QUERY function.

Count With QUERY Using OR

The QUERY function uses syntax borrowed from the SQL language, so there is a bit of a learning curve if you are used to traditional spreadsheet functions. However, this SQL structure makes the function easier to read and more flexible if you want to add parameters to it later.

Using QUERY to count with an OR
Counting With QUERY

=QUERY(A29:A34,"select count(A) where A='Bread' OR A='Apples' OR A='Milk'")

Choose What Works for You

Now you know three techniques to count cells using the OR condition. Use which makes the most sense for your spreadsheet.

Related Posts

Leave a Comment