Google Sheets – How to Sum Top Three Values in a Column

google sheetsgoogle-sheets-arrayformulagoogle-sheets-arrays

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.

Best Answer

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.