OR in COUNTIFS in Either of the Columns in Google Sheets

The Scenario

Imagine a tournament where five teams compete against each other. To keep track of the matches, we have two columns in a spreadsheet: Team (column C) and Team (column D). Here’s a visual representation:

OR in Countifs in Either of the Columns - Example

Our goal is to determine how many matches have been played by teams A or B so far. In other words, we want to find out if the text/string A or B matches in either of the columns in each row. The expected output in this example is 7, not 8.

Using COUNTIFS with OR Criteria

To count based on multiple conditions, we can use the dedicated function COUNTIFS. However, the usual way of using COUNTIFS won’t work for our scenario. Don’t worry, I’ve got you covered!

Here’s Formula 1 that solves the OR in COUNTIFS in either of the columns in Google Sheets:

=ArrayFormula(SUM(COUNTIFS(if((C2:C="A")+(C2:C="B"),"~","")&if((D2:D="A")+(D2:D="B"),"~",""),{"~","~~"})))

Now, let’s break down how this formula works.

How Does This Formula Work?

If you’re familiar with the basics of using OR in one column using a virtual array, you’ll quickly grasp the concept. The formula uses the if function to check if teams A or B match in column C and returns a tilde (~) in the matching rows. Similarly, it checks for matches in column D and returns a tilde as well.

Next, we combine both using the concatenation operator & to form our criteria range. The criteria range consists of two criteria: ~ and ~~. Finally, we use the ArrayFormula and SUM functions to get the desired count.

An Alternative Formula Using Query

If you prefer a different approach, you can use a QUERY formula to solve the OR in COUNTIFS in either of the columns’ problem. Here’s the formula:

=query(C2:D,"select count(C) where C matches 'A|B' or D matches 'A|B' label Count(C)''")

In this formula, we use the Matches regular expression match in the WHERE clause to determine the count based on our requirement.

That’s it! Now you know how to use OR criteria in COUNTIFS in either of the columns in Google Sheets.

If you’re interested in learning more about Google Sheets count functions or other related topics, you may want to check out the resources below:

I hope you found this article helpful! If you have any questions or want to learn more, feel free to explore Crawlan.com. Enjoy your Google Sheets journey!