Comma-Separated Values as Criteria in Filter Function in Google Sheets

No doubt, Regexmatch is the ultimate function to match text strings in Google Sheets. This function enables us to use comma-separated values as criteria within the Filter function in Google Sheets. To know how to do this, keep reading.

This post is about using more than one condition/criterion in a single cell reference in a Filter formula.

Since we are going to use a text function, i.e. Regexmatch, within the Filter function, we must text format our criteria column called condition1 (see the syntax below) in Filter.

That doesn’t mean you need to make any changes to your existing data. That we can do within the Regexmatch formula itself. I’ll explain that later.

Further proceeding, please refer to the Syntax below. We will use comma-separated multiple values in the condtion1 argument!

FILTER(range, condition1, [condition2, …])
NOTE: condition2 is optional

Multiple Conditions in Filter Condition1 Argument in Google Sheets

We can use multiple conditions in the argument condition1 which is actually for using one condition. The Regexmatch function enables us to do that.

Let’s see how to use comma-separated criteria (list) in Filter function in Google Sheets. I am beginning with a text column.

=filter(B2:B, regexmatch(A2:A,"A|B|C"))

This formula will filter column B if column A values are “A”, “B”, or “C”.

This is a case sensitive formula. Since the criteria are in uppercase, we can use the UPPER function to convert A2:A to uppercase and thus make the formula case insensitive.

=filter(B2:B, regexmatch(upper(A2:A),"A|B|C"))

I hope, you could now understand how to use multiple conditions in Filter condition1 argument in Google Sheets.

In concise;

To use multiple criteria separate each criterion with a pipe. Use that generated value in Regexmatch to match in the condition1 column.
That means if the multiple criteria are comma-separated-values, replace the separator (comma) with pipes. I mean criteria "A,B,C" must be used as "A|B|C".

Now, see what I am going to do in real life with comma-separated values as Filter criteria in Google Sheets.

Comma-Separated Text Criteria in Filter Function in Google Sheets

Comma-Separated Values as Text Criteria in Filter Function

The formula filters the ‘AMOUNT’ in column B if the USER’s are “A” or “B” and sums the values (AMOUNT) filtered. Let’s begin.

If we want to use a single criterion, for example, filter USER “A” we can use the standard Filter as below.

=filter(B2:B,A2:A="A")

Needless to say, the following multiple filter criteria use is wrong.

=filter(B2:B,A2:A="A,B")

Google Sheets (the formula) will return an #N/A! error, stating “No matches are found in FILTER evaluation”.

The reason the formula will only accept a single criterion in condition1.

That means the formula treats "A,B" as a single condition. So obviously there is no match for this condition in column A.

Here is the correct formula to use comma-separated values as criteria in Filter function in Google Sheets. The formula replaces commas in criterion cell D2 with pipes.

The Filter condition1, as per the above ‘wrong’ formula is A2:A="A,B" which we can replace with the below formula.

=ArrayFormula(REGEXMATCH(A2:A,substitute(substitute(D2,", ","|"),",","|")))
Note: We can exclude ArrayFormula within Filter.

So the Filter formula will be;

=sum(filter(B2:B, REGEXMATCH(A2:A,substitute(substitute(D2,", ","|"),",","|"))))

Included the SUM to sum the filtered numbers.

Formula Explanation

What does the Regexmatch formula do?

To make you understand the role of the above Regexmatch, I am entering it, just for reference, in cell G2.

Regexmatch in Filter Formula in Sheets - Logic

The Filter filters column B wherever Regexmatch returns TRUE in the corresponding row.

Actually, the above Regexmatch is as simple as below.

=ArrayFormula(REGEXMATCH(A2:A,"A|B"))

In this, "A|B" must be replaced with cell D2. But in cell D2, the criterion is a comma-separated list.

I have used the SUBSTITUTE to replace the comma with the pipe and another SUBSTITUTE to replace a comma and space with the pipe.

The reason is sometimes the comma-separated filter criteria will be entered as A, B and sometimes as A,Bin cell D2. I want my formula to return the correct result in both these cases.

Comma-Separated Number Criteria in Filter Function in Google Sheets

To use comma-separated numbers as the Filter function criteria, you just want to make one change to my earlier formula.

That’s virtually format the number column, the column in which the Regexmatch matches the criteria, to text.

How to Make a Number Column to Text Column Using a Formula in Google Sheets?

Suppose I want to convert numbers in the range A2:A to text using a formula so that functions like Regexmatch will work correctly. Use either of the given solutions below.

Option 1

=ArrayFormula(A2:A&"")

Option 2

Using To_text function.

=ArrayFormula(to_text(A2:A))

I am using the first option here. The changes, when using the comma-separated number criteria instead of similar text criteria, have been marked in the below image.

=sum(filter(B2:B, REGEXMATCH(A2:A&"",substitute(substitute(D2,", ","|"),",","|"))))

Comma-Separated Values as Number Criteria in Filter Function

Comma-Separated Date Criteria in Filter Function in Google Sheets

My advice is to try to avoid comma-separated date criteria use in Filter as it will be error-prone! Why?

See my solution first.

=sum(filter(B2:B, REGEXMATCH(to_text(A2:A),substitute(substitute(D2,", ","|"),",","|"))))

Comma-Separated Values as Date Criteria in Filter Function

In the last formula (number criteria), I have used A2:A&"" to convert a number column to text.

Here it’s to_text(A2:A) because we want to convert a date column to text without losing the ‘format’.

Please note I’ve used the word ‘format’ in a different meaning. The first formula will convert the date to text in date value format. The second formula will also convert the date to text but in a date format.

To understand this key the following two formulas in cell F1 and G1 respectively.

=A2&""

=to_text(A2)

Why I am not recommending comma-separated date criteria use in Filter? To understand, please go through the usage notes below.

Usage Notes

When using comma-separated dates as criteria in Filter, make sure one thing. That is, the dates in the date column (here A2:A) and the criteria cell (here D2) must follow the same format.

If the dates are in MM/DD/YYYY format the criteria also should be in this format.

I mean 23/01/2020, 23/1/2020 and 23/1/20 are different date formats, hence they won’t match.