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?

We need to see the error in order to help you.

TylerH
– TylerH

2024-09-09 15:27:00 +00:00
Commented Sep 9, 2024 at 15:27 — TylerH
– TylerH, Commented Sep 9, 2024 at 15:27

z.. · Accepted Answer · 2024-09-09 15:22:52Z

1

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

edited Sep 9, 2024 at 15:22

answered Sep 9, 2024 at 15:16

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Toastlover · Accepted Answer · 2024-09-09 15:36:45Z

0

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

answered Sep 9, 2024 at 15:36

Toastlover

918 bronze badges

1 Comment

John Slegers Over a year ago

Thanks for giving me the correct direction. This one worked out for me : =QUERY(Data!A:I; "SELECT SUM(G) WHERE LOWER(B) MATCHES '.*(jumbo|lidl|albert|picnic).*' AND F = 'Af' LABEL SUM(G) ''"; 1)

Haluk · Accepted Answer · 2024-09-09 16:13:18Z

0

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) ''")

edited Sep 9, 2024 at 16:13

answered Sep 9, 2024 at 15:51

Haluk

1,5462 gold badges6 silver badges18 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related