CODEWITHSUNDEEP
google-sheets
user148177
user148177

Reputation: 133

How to sum largest $n$ values in a range in Google Spreadsheet?

I have a list of values and I need to sum the largest 10 values (in a row). I found this but I can't figure it out/get it to work: https://productforums.google.com/forum/#!topic/docs/A5jiMqkRLYE

Upvotes: 3

Views: 8848

Answers (3)

wsaleem
wsaleem

Reputation: 642

The sortn function seems to be just what you need.

From the documentation linked above, it "[r]eturns the first n items in a data set after performing a sort." The data set does not have to be sorted. It takes a bunch of optional parameters as it can sort on multiple columns.

SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)

The interesting ones for your case are n, sort_column1, and is_ascending1. Specifically, your required formula would be

sum(sortn(transpose(A3:O3), 10, 0, 1, false)))

Some notes:

  • This assumes your data in A3:O3. You can replace it with your range.
  • transpose converts the data row to a data column as required by sortn.
  • 10 is n, indicating the number of values that you require.
  • 0 is the value for display_ties_mode. We are ignoring this value.
  • 1 is the value of sort_column1, telling that we want to sort the first column (after transpose).
  • false tells sortn to sort descending and thus pick the largest values. The default is to pick the smallest.

Upvotes: 0

Vladimir Ilcic
Vladimir Ilcic

Reputation: 1

This works in old google sheets too:

sum(query(sort(transpose($A3:$O3), 1, false), "select * limit 10"))

Transpose puts the data in a column, sort sorts the data in a descending order and then query selects first 10 numbers.

Unfortunately, replacing sort with "order by" in a query statement does not work, because you can not reference a column in a range returned by transpose.

Upvotes: 0

JPV
JPV

Reputation: 27262

let's say you want to sum the 10 highest values of the range E2:EP then try: 

=sumif(E2:P2, ">="&large(E2:P2,10))

and see if that works ?

EDIT: Maybe this is a better option ? This will only sum the 10 outputted by the array_constrain. Will only work in the new google sheets, though..

=sum(array_constrain(sort(transpose($A3:$O3), 1, 0), 10 ,1))

Can you see if this works ?

Upvotes: 7

Related Questions