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”))))}

• Use Excel List for Data Analysis What make data analysis become easier? If we recognize features of Microsoft Excel, we found that create a list is...
• Why Protecting Your Privacy and Personal Data is so Important Is it really necessary to be a fanatic about protecting your personal information and keeping your private data private? Well,...
• Canon EOS 550D (Rebel T2i / X4 Digital) Preview [/caption] Preview based on a pre-production Canon EOS 55OD Less than a year after the launch of the EOS Rebel...
• 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...
• 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...