Ask Your Question

# 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

## Comments

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

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.

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

## 1 Answer

Sort by » oldest newest most voted

Hi

Try something like:

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


Regards

more

## Comments

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

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

## Stats

Asked: 2016-02-16 18:26:25 +0200

Seen: 1,218 times

Last updated: Feb 28 '16