Is it possible to click and drag queries?

I describe my problem below and also have a reference sheet.

cells A1:B5

Category Withdrawal
CatA 4
CatB 2
CatA 6
CatB 11

cells C1:C3

Check For

I want to put the following query into cell D1 and drag it down to D2: =query(A2:B5,"select sum(B) where A = '"&C2&"'",0), but I get the following error: Array result was not expanded because it would overwrite data in D2.

How can I extend the query so each subsequent looks at the next 'Check For' cell?

2 Answers 2


Instead of

=query(A2:B5,"select sum(B) where A = '"&C2&"'",0)


=query($A$2:$B$5,"select sum(B) where A = '"&C2&"' label sum(B) ''",0)
  • label sum(B) '' prevents that sum(B) be added as a column header, so now the formula will return a single value which will allow you to drag down your the formula.

  • $A$2:$B$5 will prevent that the reference be changed will dragging the formula.


When copying formulas by dragging or copy & pasting cells / ranges it's important to know how references work in Google Sheets.

  • A1 is a relative reference. This type of reference will change as the formula is dragged. If you drag it down, A1 will be automatically changed to A2, A3, and so on. If you drag it right, A1 will be automatically changed to B1, C1, and so on.
  • $A$1 is an absolute reference. This type of reference will be kept the same.
  • It's possible to have one of the dimensions as absolute while the other as relative i.e. on $A1 the column dimension is absolute while the row dimension is relative.



You can use

=query($A$2:$B,"select sum(B) where A = '"&C2&"' label sum(B) '' ",0)

and drag it down for as many rows as needed.
Please notice how we also need to lock the range $A$2:$B. We would otherwise may get strange results.

As an alternative use a single


enter image description here

  • 1
    I see you have accepted the answer by Ruben. Be very careful. The formula may lead to wrong results. You need to lock the ranges. See what happens if you don't. Commented Apr 24, 2021 at 4:37
  • Thanks for the feedback :) Commented Apr 24, 2021 at 18:02
  • A warning to the OP and future users. That was my comment. Still. It is amazing -though not quite ethical (especially coming from a lately elected moderator)- how your following actions of editing your answer again and again and again are structured towards primarily serving yourself than the users. Glad I could help :) Commented Apr 25, 2021 at 1:35
  • There are no ethical issues on improving a post on the contrary it's encouraged to do so. Commented Apr 25, 2021 at 2:07

Your Answer

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

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