# Calc - Sum of top values in a row [Closed]

The answer question " How to select the top "values" in each row of the spreadsheet [closed]" explained how to identify to top n items in a row.

I want to SUM the top 15 values in a row containing up to 30 values I can do this in two ways (both long winded) [values held in $B2:$AR2]

1. create 15 new columns and populate them with "=LARGE($B2:$AR2,n)" where n = 1-15 and then sum the 15 columns
2. create a single total column with a formula of 15 expressions =(LARGE($B2:$AR2,1)+LARGE($B2:$AR2,2)+LARGE($B2:$AR2,3)+ .....LARGE($B2:$AR2,15)) this gets very unwieldy for the top 50 !

is there a better way of doing this in Calc

In Excel I can use the formula =SUM(LARGE($B2:$AR2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) in calc this just returns the highest value.

(Editing: I ('Lupp') only added the link to the post referred to at the beginning of this question.)

edit retag close merge delete

How about the previously to sort the row and simplify sum the first (or last) 15 values?

( 2016-02-17 04:53:27 +0200 )edit

I probably didn't describe the problem well enough. The spreadsheet contains the weekly scores of up to 20 or 30 players in a golf competition running over 30 weeks. The best 15 scores (out of a possible 30) count. Each week the scores are entered into the sheet and I would like the calculation to be automatically carried out. The sort & sum method will work ok but requires multiple operations. The Excel solution has a single cell for each row calculating the value we want.

( 2016-02-17 10:45:15 +0200 )edit

Well, then use the formula pierre-yves samyn - it succinctly and elegantly. Just change the range A1:A15 on absolute reference A$1:A$15 - this will avoid possible errors when the formula moving from cell to cell

( 2016-02-17 12:11:04 +0200 )edit

Sort by » oldest newest most voted

Hi

Try something like:

=SUMPRODUCT(LARGE($B2:$AR2;ROW(A1:A15)))


Regards

more

Thanks (and to JohnSUN) - gives me exactly what I want. I'm now trying to work out how it works - good for the brain !

( 2016-02-17 13:36:16 +0200 )edit

Thank's for the feedback... and very sorry for the lack of explanation.

ROW(A$1:A$15) returns an array containing the numbers 1 to 15. @JohnSUN is absolutely right to recommend the passage to absolute references.

SUMPRODUCT works with arrays, so no need to validate by Ctrl+Shift+Enter

Regards

( 2016-02-17 13:59:57 +0200 )edit