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.
Contents
Video Tutorial
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.
ℹ️ 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 gets long. Let’s look at an alternative that stays smaller as you add more conditions.
Insert Math Symbols (Add-On)
Count With an OR Condition With ARRAYFORMULA
The following technique involves a function called ARRAYFORMULA. ARRAYFORMULA repeats functions, repeating the SUM and COUNTIF functions.
=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 it by commas. However, the formula is 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.
=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.