I have a google sheet (Final Calculation) where I am trying to get sum of all values in Column G (in Data tab) against a set of values in Column B, L and D (in Data tab). However, condition for values in Column D is dynamic and would likely change every month. I have created a tab Advertiser List where the condition for filter in Column D gets updated. How, do I use this condition in Query function used in C5 cell in Final Calculation tab?

I tried referencing the condition in Advertiser List sheet using indirect but I am getting an error. I have also added a hard coded formula is other cells in Final Calculation tab to show what am I trying to achieve.

Below is the formula that I have written and is showing an error: =iferror(QUERY(Data!$A:$L, "Select Sum(G) where B = '"&$B5&"' and L = '"&C$3&"' and indirect('"Advertiser List"'&'"!"'&'"D2"') label sum(G)'' "),0)

Here is the link of the Google Sheet (Link)

CodePudding user response：

You don't need INDIRECT to get a text value from another tab:

=QUERY(Data!$A:$L, "Select Sum(G) where B = '"&$B5&"' and L = '"&C$3&"' and (" & 'Advertiser List'!D1 & ") label sum(G)'' ")

When in doubt, try to build the "select ... " string in a separate cell until you see the expected result. Then wrap it with query and check again. Adding IFERROR should always be your last step, because you will want to see the errors that your query is throwing.