In this tutorial, we count cells based on values in two columns. The conditions for the counting are the presence of specific values in one column OR another column. This counting will be easier to understand with the following example.
Make a copy of this Google Sheet with the example to follow along.
Table With Two Columns
Here is a simple table to illustrate our problem.
The green checkmarks indicate the rows we want to count. They have an
y in columns
Two Functions That Won’t Work
If you are trying to add two COUNTIF functions or use the COUNTIFS function, you’re not alone. These are the first solutions that come to mind. However, adding two COUNTIF functions would produce
4 due to double-counting row
The COUNTIFS function uses AND comparisons instead of OR and therefore would output
1 as it would only count row
3 since row
3 is the only row with both an
x and a
Counting Cells With the QUERY Function
With that out of the way, let’s focus on the solution to this problem. We are going to use the versatile QUERY function to count cells correctly. QUERY uses the same syntax as the SQL language instead of typical spreadsheet formulas, so it will look different than what you have seen in the past.
The QUERY function correctly counts three rows with an
x or a
y. The syntax is as follows.
=QUERY(A2:B5,"select count(A) where A='x' OR B='y'")
The query counts rows in
A2:B5 where there is an
y. It produces the output you see in
A7:A8 in the image above.
ℹ️ Keep in mind that QUERY is case-sensitive. Therefore, it would not count an upper case
Removing the Count Label
If you want to remove the header that says
count, you can add the
label clause with a blank value.
=QUERY(A2:B5,"select count(A) where A='x' OR B='y' label count(A) ''")
Using the QUERY function solves many problems like this in spreadsheets. Learn more about it here.