3

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
CatA
CatB

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?

3

Instead of

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

use

=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.

NOTE:

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.

Related

4

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

=ArrayFormula(IF(LEN(C2:C),SUMIF($A$2:$A,"="&C2:C,$B$2:$B),""))

enter image description here

4
  • 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. Apr 24 '21 at 4:37
  • Thanks for the feedback :)
    – Rubén
    Apr 24 '21 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 :) Apr 25 '21 at 1:35
  • There are no ethical issues on improving a post on the contrary it's encouraged to do so.
    – Rubén
    Apr 25 '21 at 2:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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