0

I'm trying to write a query formula in google sheets using where/contains to find values in another sheet that correspond with the text in column A of the original sheet. Since there will be hundreds or thousands of columns, I don't want to manually enter the text to search for in each row: is there a way to input the text of another cell as a variable?

For reference, the formula structure is:

=Sum(query('SheetName'!A:D, "SELECT C Where B Contains 'text1'"))

I'd like to replace 'text1' with a cell reference so I don't have to actually input it.

There is also likely a way better way to do this overall, but this is what I've got so far. thanks!

Improve this question
2
  • 1
    use double quotes and ampersands to refer to cell query('SheetName'!A:D, "SELECT C Where B Contains ' "&DifferentSheetName!A1&" ' ",0)
    – Oleg_S
    Dec 16, 2021 at 8:37
  • Welcome to Web Applications Stack Exchange. Consider sharing a publicly editable sample spreadsheet with realistic-looking data. Show your hand-entered expected results in the sample spreadsheet.
    – doubleunary
    Dec 16, 2021 at 8:41

1 Answer 1

Reset to default
1

Use string concatenation to build the query statement, like this:

=query( 
  SheetName!B2:C, 
  "select sum(C) 
   where B contains '" & trim(A2) & "' 
   label sum(C) '' ", 
  0 
)
Improve this answer
3
  • Where does this query reference the value of text1 as mentioned in the question?
    – Tedinoz
    Dec 17, 2021 at 10:32
  • The OP wrote: "I'd like to replace 'text1' with a cell reference". The formula references a cell.
    – doubleunary
    Dec 17, 2021 at 10:40
  • OK, I get it - the user enters their "search" term in Cell A2 of the same sheet in which the query is entered.
    – Tedinoz
    Dec 17, 2021 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.