In some situations, you may need to sum the n largest values in a range—for example, the top 10 values. If your data resides in a table, you can use autofiltering to hide all but the top n rows and then display the sum of the visible data in the table’s total row.

Another approach is to sort the range in descending order and then use the SUM function with an argument consisting of the first n values in the sorted range. A better solution—which doesn’t require a table or sorting—uses an array formula like this one:

{=SUM(LARGE(Data,{1,2,3,4,5,6,7,8,9,10}))}

This formula sums the ten largest values in a range named Data. To sum the 10 smallest values, use the SMALL function instead of the LARGE function:

{=SUM(SMALL(Data,{1,2,3,4,5,6,7,8,9,10}))}

These formulas use an array constant comprised of the arguments for the LARGE or SMALL function. If the value of n for your top-n calculation is large, you may prefer to use the following variation. This formula returns the sum of the top 30 values in the Data range. You can, of course, substitute a different value for 30.

{=SUM(LARGE(Data,ROW(INDIRECT(“1:30”))))}

• January 2010, Snow Cover, EA, AO, OIV2 SSTA Other data of January, beginning with the AO (Arctic Oscillation). New value very negative in January after the record in...
• All Intelligent Investing Is Value Investing Today's post is courtesy of Wealth Building Lessons. This past year, the stock market has seen incredibly volatile swings. My...
• The Importance Of Backing Up Your Blog Backups in every computing endeavor just make good sense. Without your good, reliable backups, you're likely to go through the...
• Linda's Gluten-Free Meatloaf Linda's Gluten-Free Meatloaf (Serves 5 to 6 Adults) Ingredients: 2 to 2 & 1/2 Pounds of Lean Ground Beef. 1...
• Watson and Prugh in Lead at Bob Hope Classic At the Bob Hope Classic in La Quinta, California, where no one could ask for more perfect sunny and warm...