CODEWITHSUNDEEP
google-sheets
The Singularity
The Singularity

Reputation: 2698

How do I get Row Totals using QUERY() in Google Sheets?

I'm working with the following Google Sheet.

Sheet2 uses the following QUERY() function to retrieve data from Sheet1

=QUERY(IMPORTRANGE("1s8krJ7rbZ1DMblZ3vdLcG5pySVM3ESCBy1o7R5Zv4LM", "Sheet1!B3:D"))

Is it possible to return the Row Totals (For Example: B4+C4+D4 for Row 4) using the above QUERY() function?

Please Advise.

My Query and Expected Output are Outlined on the Google Sheet.

Upvotes: 0

Views: 147

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9345

Be aware that while a simple QUERY can return the results, it will actually take up the entire column if you don't limit it. In other words, all the null rows from Sheet1! B:D will also come over with the QUERY. If you want only the results that have numbers, try something like this:

=QUERY(Sheet1!B3:D, "Select B+C+D WHERE B+C+D is not null label B+C+D ''")

Or you could use MMULT like this:

=MMULT(FILTER(Sheet1!B3:D,Sheet1!B3:B<>""),SEQUENCE(3,1,1,0))

The results may look the same whether limited or not. But in a QUERY or MMULT without limitations, you won't be able to use the space below the visible results for anything. I only mention this because, currently in your sheet, you do have data (a TRANSPOSE formula) below the main results. If you won't in your real sheet and don't care about the are below the visible results being inaccessible to other data or formula entry, then you don't need to limit.

Upvotes: 0

JPV
JPV

Reputation: 27262

You should be able to do something like this:

=QUERY(IMPORTRANGE("1s8krJ7rbZ1DMblZ3vdLcG5pySVM3ESCBy1o7R5Zv4LM", "Sheet1!B3:D"), "Select Col1+Col2+Col3 label Col1+Col2+Col3 ''")

Note that importing from another tab in the same spreadsheet doesn't require importrange. In that case, this should also work:

=QUERY(Sheet1!B3:D, "Select B+C+D label B+C+D ''")

Another way, to achieve the same result would be

=ArrayFormula(if(len(Sheet1!B3:B), Mmult(--Sheet1!B3:D, transpose(column(Sheet1!B2:D2)^0)),))

Upvotes: 1

Related Questions