Query function refering to cell value

This post is in continuation of my earlier post on query function. for basic understand the query function have a look at the following link https://anandexcels.wordpress.com/2013/11/01/query-function-in-google-sheets/

Now we look at the referring to cell value within query function so that query function will be more robust.

(Q) What is sales value during the period for Britney

we will continue use our old data set

(A) The formula we are using is

=QUERY(A:D;”select B, Sum(C) where B ='”&F1&”‘ group by B label Sum(C) ‘Sales'”;1)

the results will be

where cell F1 contains the criteria in our example Britney

now we do not need to change the formula, if you change the cell value of F1 to from Britney to Paris the query function recalculates the values

the results will be



(1) Remember query function is case sensitive

(2) if you use paris instead of Paris you may get the null string without any error

(3) observe the syntax after where B =

(4) if Cell F1 is string you have use single-quote double-quote ampersand then F1 similarly after F1 also, this is a syntax you have to follow