Why does my Google Sheets query simplificaiton result in an error?

Question

I have a Google sheet question. I use a formula to sum all values of a column that contains keywords in an other column. I use this formula :

=QUERY(Data!A1:I; "SELECT SUM(G) WHERE (LOWER(B) CONTAINS 'jumbo' OR LOWER(B) CONTAINS 'lidl' OR LOWER(B) CONTAINS 'albert' OR LOWER(B) CONTAINS 'picnic') AND F = 'Af' LABEL SUM(G)''"; 1)

I tried to simplify the formula to this :

=QUERY(Data!A:I, "SELECT SUM(G) WHERE LOWER(B) MATCHES 'jumbo|lidl|albert|picnic'", 1)

But it gives an error. Is the first formula still the one to go or are there other options?

Answer 1

Your second formula gives an error because the MATCHES operator in Google Sheets is case-sensitive and requires the use of regular expressions. To fix it you can modify the query to include (?i) to make the regular expression case-insensitive. Additionally, you need to keep the F = 'Af' condition from your first query if it’s still required.

Here 2 simplified versions of your formula using MATCHES:

=QUERY(Data!A:I, "SELECT SUM(G) WHERE LOWER(B) MATCHES '(?i)jumbo|lidl|albert|picnic' AND F = 'Af'", 1)

=QUERY(Data!A:I, "SELECT SUM(G) WHERE LOWER(B) MATCHES '.*(jumbo|lidl|albert|picnic).*' AND F = 'Af'", 1)

This should work by matching the keywords in column B and summing the corresponding values in column G where column F equals "Af".