OR Logic in COUNTIFS Across Either Column in Google Sheets

This post explains how to use OR logic in COUNTIFS across either column in Google Sheets. The best way to solve this is by using QUERY.

If you wish to use COUNTIFS, you may need to use it unconventionally. Here, I’ll explain that and include the QUERY solution. Before that, here is the scenario to understand the problem.

Assume five players compete in a tournament, and their fictional names are A, B, C, D, and E.

In my sample data, C2:D contains player names where each player in C2:C will compete with the corresponding player in D2:D by the end of the tournament.

I want to find the total matches played by players A and B by counting rows where either C2:C or D2:D contains A or B—in other words, applying OR logic in COUNTIFS across either column.

Sample Data: Players and Matches

Here is how to find it.

OR Logic in COUNTIFS Across Either Column

We can’t apply the OR function in an array without using resource-hungry Lambda helper functions. So we will use the + operator.

The following formula will return either 0, 1, or 2 depending on the presence of A or B in cells C2 and D2:

=(C2="A")+(C2="B")+(D2="A")+(D2="B")

For example, with the sample data, the output will be:

=TRUE+FALSE+FALSE+TRUE

Where TRUE = 1 and FALSE = 0, so the result will be 2. This means if the value is greater than 0, then either A or B is present.

We need to apply this to the range C2:C and D2:D. So we require the ARRAYFORMULA:

=ArrayFormula((C2:C="A")+(C2:C="B")+(D2:D="A")+(D2:D="B"))
Example of OR Logic in COUNTIFS Across Either Column

In COUNTIFS, use this as the criteria range and “>0” as the criterion:

=COUNTIFS(ArrayFormula((C2:C="A")+(C2:C="B")+(D2:D="A")+(D2:D="B")), ">0")

The formula will return 7, indicating that players A and B played 7 matches.

This is an example of applying OR logic in COUNTIFS across either column in Google Sheets.

QUERY Alternative

We can use the QUERY function as an alternative to replace OR in COUNTIFS across either column in Google Sheets.

The QUERY function applies an SQL-like query to the range C2:D.

Formula:

=QUERY(C2:D, "SELECT COUNT(C) WHERE C MATCHES 'A|B' OR D MATCHES 'A|B' LABEL COUNT(C)'' ")

This formula uses the QUERY function to count the number of rows in the range C2:D where either column C or column D matches ‘A’ or ‘B’.

It uses the MATCHES regular expression match to filter rows where columns C or D match either ‘A’ or ‘B’.

The LABEL clause in the formula removes the default label from the result.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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