Summing top three values in a column from a Google sheet

Question

I have a Google Sheet with a variable number of data rows. I would like to sum the three highest values from a certain column, but they won't necessarily be in the top three cells. An empty cell should be treated as zero, if there are only one or two rows with data so far.

How can I achieve this? The closest thing I can think to do is MAX(A1:A2000) but that only gets the one very highest value, without accounting for the second- and third-highest.

Answer 1

Formula

=ArrayFormula(SUM(IFERROR(LARGE(A1:A2000,{1,2,3}),0)))

Explanation

• LARGE returns the nth greater element.
• IFERROR if the first argument returns an error, returns the second argument.
• {1,2,3} is an array of literals. See Using arrays in Google Sheets.
• ArrayFormula makes that functions like LARGE and IFERROR return an array of values.

Answer 2

You probably want to look at LARGE().

Returns the nth largest element from a data set, where n is user-defined.

The simplest, but by no means most elegant, solution I have is:

=if(iserror(large({data},1)),0,large({data},1))+
if(iserror(large({data},2)),0,large({data},2))+
if(iserror(large({data},3)),0,large({data},3))

(carriage returns added for readability)

The if and iserror are there to return a 0 if the function throws an error. (For instance, if there are only two values in your data.)

Of course, large({data},1) is functionally equivalent to max({data}), but I left it that way for consistency.