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