CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-query-language
John Slegers
John Slegers

Reputation: 13

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

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?

Upvotes: 0

Views: 88

Answers (3)

Haluk
Haluk

Reputation: 1546

If range K2:K10 is housing your keywords, another alternative is;

=QUERY(A2:I,"Select Sum(G) Where Lower(B) Matches '" & TEXTJOIN("|", TRUE, K2:K10) & "' Label Sum(G) ''")

And/Or;

=QUERY(A2:I,"Select Sum(G) Where F= 'Af' And Lower(B) Matches '" & TEXTJOIN("|", TRUE, K2:K10) & "' Label Sum(G) ''")

Upvotes: 0

Toastlover
Toastlover

Reputation: 81

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".

Upvotes: 0

z..
z..

Reputation: 12858

The regex engine used in the QUERY function assumes that the start and end of line anchors are present (i.e. matches 'foo' is the same as matches '^foo$'). Which means that the equivalent of contains 'foo' is matches '.*foo.*' not matches 'foo'.

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

You could also do this using the SUMPRODUCT function.

=SUMPRODUCT(Data!G:G,REGEXMATCH(Data!B:B,"(?i)jumbo|lidl|albert|picnic"))

Upvotes: 2

Related Questions