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!