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.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

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.