U
U
UncleD2019-07-02 00:01:59
Google Sheets
UncleD, 2019-07-02 00:01:59

How to sum an array column in Google Sheets?

Greetings.
There are three Google tables ( Table1 , Table2 ), two of which are filled in by users, and the third brings the data from the first two into an array.
The query in the third table looks like this:

=QUERY(

{
IMPORTRANGE("1b0Wx_IoKIts1NcRttBbu93ckxEBJeE2_R1GZsHNhlDE";"A1:F10")\
IMPORTRANGE("1zAxG4IsjOfV0uSDWgGYAxYVvruaqDtmH9hSUJke7xfE";"A1:E10")
};

"select 
Col1,
Col3,
Col5*Col11,
Col2,
Col11,
Col6,
Col6*Col11

where Col11>0

LABEL
Col1 '1',
Col3 '2',
Col5*Col11 '3',
Col2 '4',
Col11 '5',
Col6 '6',
Col6*Col11 '7'"
)

Everything works great until the accountant starts wanting a line at the end of the table for the TOTAL for the last column. That is, after the last line in column A there is the word "TOTAL", although it is possible without it, and in column G the sum of all its values, which in this example should be equal to 108.
Any attempts to describe a new array in QUERY using formulas or even pulling it up from a new sheet lead to the appearance of a cyclic function.
So far, the issue has been solved with a crutch in the form of creating a copy of the table with an array, calculating the sum on another sheet and adding this line to the end of the array. However, this is not pretty and I want to find a more elegant solution.
Any ideas on what to read?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
N
Noir, 2019-07-02
@PolarBearGG

https://docs.google.com/spreadsheets/d/1wUN4cl_65Q...
might help

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question