Count Rows With OR Condition in Two Columns- Google Sheets

In this tutorial, we count rows based on values in two columns. The conditions for the counting are the presence of specific values in one column OR another. This counting will be easier to understand with the following example.

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

Video Tutorial

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 x OR y in columns A OR B.

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 3.

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 y.

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 x or 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 Y.

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) ''")

Conclusion

Using the QUERY function solves many problems like this in spreadsheets. Learn more about it here.

Related Posts