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?
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"))
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”.
1
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) ''")