In this tutorial, let me walk you through how to use the OR criterion in multiple columns in COUNTIFS in Google Sheets.

In conditional count, it’s easy to use multiple OR conditions (this or that) in one column.

I have explained that in one of my earlier Countifs-related tutorials here – Countifs with Multiple Criteria in Same Range in Google Sheets.

For example, we want to count column D if the text in that column is painting or driving.

In that case, we can use the below Array_Formula, Sum, and Countifs combination.

Formula # 1 – Correct ✔

=ArrayFormula(sum(countifs(D:D,{"Driving","Painting"})))

But the same trick may not work in multiple columns.

I know you badly require an explanation for this. Here you go!

Assume you want to extend the above formula to one more column.

That is, count column E for favorite authors Leo Tolstoy and Jane Austen from among a bunch of authors.

The following formula is not going to work!

Formula # 2 – Incorrect ✖

=ArrayFormula(sum(countifs(D:D,{"Driving","Painting"},E:E,{"Leo Tolstoy","Jane Austen"})))

Using OR in multiple columns in Countifs is quite tricky in Google Sheets.

We will use Regexreplace with the above functions (except Sum) for that. In this post, you can learn this trick.

Using OR in Multiple Columns in COUNTIFS

We have seen two OR in multiple column Countifs formulas above – one working and another non-working.

Here is the sample data used in those formulas.

First of all, we will rewrite formula # 1, which is already returning the correct result, using our new method.

That will help us to rewrite the non-working formula # 2 easily. Before that, let’s understand the logic quickly.

Regexreplace in Countifs (Logic)

The first formula counts the text criteria Driving and Painting in column D.

Here we will substitute both criteria with a unique string, symbol, or character.

It’s up to you to choose the replace text/character/symbol. In my example, I am going to use the character “A.”

We will use the REGEXREPLACE function for the substitution task.

Then using Countifs, we will count the criterion “A” instead of the multiple OR criteria, i.e., Driving and Painting.

Syntax:

COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

Note:- The arguments within the square brackets are optional, and we don’t want them in our formula # 1.

Generic Formula # 1:

ARRAYFORMULA(COUNTIFS(regexreplace_formula_1, "A"))

criteria_range1 – regexreplace_formula_1

criterion1 – “A”

If there is one more column, we will do the substitution in that column also. But, the criteria will be substituted with another value, for example, “B.”

Generic Formula # 2:

ARRAYFORMULA(COUNTIFS(regexreplace_formula_1, "A",regexreplace_formula_2, "B"))

Note:- As already mentioned, the arguments within the square brackets are optional, and we will use them in our formula # 2.

Let’s apply OR in multiple columns in Countifs.

Formula Examples to Using OR in Multiple Columns in COUNTIFS

I have explained everything related to OR criteria in multiple columns in Countifs in Google Sheets.

Here is the replacement of Formula # 1 as per Generic Formula # 1.

One Column:

=ArrayFormula(countifs(regexreplace(D2:D,"(?i)painting|driving","A"),"A"))

Formula # 2 as per Generic Formula # 2 (it’s the replacement of our non-working formula given at the top of this tutorial).

Two Columns:

=ArrayFormula(countifs(regexreplace(D2:D,"(?i)painting|driving","A"),"A",regexreplace(E2:E,"(?i)Leo Tolstoy|Jane Austen","B"),"B"))

What about OR in a third column in Countifs?

I know, now you can do that yourself. I am leaving the generic formula for you to make it easier.

Three Columns:

ARRAYFORMULA(COUNTIFS(regexreplace_formula_1, "A",regexreplace_formula_2, "B",regexreplace_formula_3, "C"))

That’s all. Thanks for the stay. Enjoy!

Related Resources

• Google Sheets: Countifs with Not Equal to in Infinite Ranges.
• How To Use Countif or Countifs In Merged Cells In Google Sheets.
• COUNTIFS in a Time Range in Google Sheets [Date and Time Column].
• Not Blank as a Condition in Countifs in Google Sheets.
• Countif | Countifs Excluding Hidden Rows in Google Sheets.
• Varying Array Sizes in Countifs in Google Sheets.