Google-sheets – How to SUM LARGE 5 largest elements out of 8 numbers in a row

formulasgoogle sheetsgoogle-sheets-arrayformula

In Excel I am able to get the output using this formula:

=SUM(LARGE(C5:L5,{1,2,3,4,5}))

But in Google Sheets I am unable to get the right answer. Can anyone please help?

In the image below, observed 7th row the sum must be 49/50 using the technique =SUM(LARGE(C7:L7,{1,2,3,4,5})) but the solution is showing as “10” instead of “49”.

Best Answer

You need to use an array instead

=ArrayFormula(SUM(LARGE(C7:L7,{1,2,3,4,5})))

Functions used:

Main point

You can't filter by the sum within the query that forms that sum. But you can run another query on the output of the first one. Like this:

=query(query(....), "select * where Col2 <> 0")

Other remarks

I don't see the need for index command here.
Instead of A2:B1000, use A2:B to capture all rows to the bottom of the sheet.
It is better to include the header row in the queried range, designating it as a header with the third parameter (number of queried rows). That is, query(A1:B, "select...", 1) is preferable to query(A2:B, "select..."). When you don't provide the third parameter, the command guesses whether the top row is header or data; and it will sometimes guess wrong.
I don't think you need "and B is not null", as null values will be ignored by the sum anyway.

So, my version of the command is

=query(query(A1:B, "select A, sum(B) where A is not null group by A order by sum(B) desc", 1), "select * where Col2 <> 0", 1)

Best Answer

Related Solutions

Google-sheets – Sum rows of a array function that outputs a matrix [Google Spreadsheet]

Google Sheets – Get Sums by Group and Output Only Non-Zero Sums

Main point

Other remarks

Related Topic