How can you query and SUM total a Column name, rather than "=SUM(A:A)" in Sheets?

Question

• I want to create a query that uses SUM to add total all values in a column, into a single cell, using a distinct column name.

Sometimes the Column may move when new inventory is added, causing the reference to fail/not match...

I want the reference to connect to a distinct column name, not "A:A"

From my research, it seems the answer is along the lines of ADDRESS() and/or MATCH()

But here's the simple Query and Issues, referencing to column "A":

=QUERY(Sheet2!A:A, "select sum (A) label sum (A) ''")

You can also see I set the label to '' so it only returned the value.

Any help or advice to help pipe the API data into a main sheet would be appreciated.

Answer 1

Given your sample sheet names and setup, delete everything from Sheet1, B2:B (i.e., all QUERY formulas). Then place the following formula into Sheet1!B2:

=ArrayFormula(IFERROR(VLOOKUP(FILTER(A2:A,A2:A<>""),TRANSPOSE(FILTER({Sheet2!1:1;MMULT(SEQUENCE(1,ROWS(Sheet2!A2:A),1,0),INDIRECT("Sheet2!2:"&ROWS(Sheet2!A:A))*1)},Sheet2!1:1<>"")),2,FALSE),0))

It is unclear from your post where the labels "Apple" and "Pear" in Sheet1 must stay in the order they appear. If they do not, you could simplify the formula. In this case, you would delete Sheet1!A2:B (i.e., all labels and formulas) and place the following formula in Sheet1!A2:

=ArrayFormula(TRANSPOSE(FILTER({Sheet2!1:1;MMULT(SEQUENCE(1,ROWS(Sheet2!A2:A),1,0),INDIRECT("Sheet2!2:"&ROWS(Sheet2!A:A))*1)},Sheet2!1:1<>"")))

Either formula will produce all of the results for all categories.

Note that both formulas have one instance of INDIRECT. This is to make the formulas truly dynamic, taking into account however many rows and columns you may have in Sheet2. But this also means that this one INDIRECT reference to Sheet2 is no longer dynamic. So if you change the name of Sheet2 to something else, while all of the other references will automatically adjust, this one will not. You will need to manually change that one INDIRECT sheet name reference in the formula if you do change the name of Sheet2 to something else.