## How to sum largest or smallest 3 values in a list of Excel?

It is common for us to add up a range of numbers by using the SUM function, but sometimes, we need to sum the largest or smallest 3, 10 or n numbers in a range, this may be a complicated task. Today I introduce you some formulas to solve this problem.

**Sum the largest / smallest 3 values in a range with formulas**

**Sum absolute values in a list with Kutools for Excel**

#### **Sum the largest / smallest 3 values in a range with formulas**

Supposing you have the following range of data need to sum the largest or smallest 3 values.

Here I can talk about some formulas for you:

**1. LARGE function:**

You need the LARGE formula three times and then sums the results when you want to sum the top 3 values, do as this:

Enter this LARGE formula into a blank cell **=LARGE(A1:D10,1)+ LARGE(A1:D10,2)+ LARGE(A1:D10,3)**, see screenshot:

And then press **Enter** key, you will get the result.

**Notes:**

(1.) If you want to sum the largest 5 values, you need to use it 5 time as this: **=LARGE(A1:D10,1)+LARGE(A1:D10,2)+LARGE(A1:D10,3)+LARGE(A1:D10,4)+ LARGE(A1:D10,5)** ,you can expand this formula as you need.

(2.) Use the following SMALL function, you can get the result of the smallest 3 values: **=SMALL(A1:D10,1)+SMALL(A1:D10,2)+ SMALL(A1:D10,3)**.

**2. Array formulas:**

You can also use the following array formulas:

Enter this formula into a blank cell, **=SUM(LARGE(A1:D10,{1,2,3}))**, and then press **Ctrl + Shift + Enter** keys to get your result .

This formula will become unwieldy as n gets larger. For example, to sum the top 20 values in a range, a formula must contain a list of integers from 1 to 20. Here is a quicker and more convenient array formula: =**SUM(LARGE(A1:D10,ROW(INDIRECT ("1:20")))).**

After enter this formula, remember to press **Ctrl + Shift + Enter** keys. If you need to sum a different quantity of numbers, just change the 20 to the desired number.

**Note:** You can also add up the smallest 3 values with these array formulas as above steps:

**=SUM(SMALL(A1:D10,{1,2,3}))**

**=SUM(SMALL(A1:D10,ROW(INDIRECT ("1:3"))))**

#### ** Sum absolute values in a list with Kutools for Excel**

Maybe in some case, you have a list of values which contains positive and negative values, and you just want to sum all the absolute values of them up, and how can you quickly get it done?** Kutools for Excel**'s **Sum absolute values** function can do a favor for you.

Kutools for Excel, with more than
300 handy functions, makes your jobs easier. |
||

After installing Kutools for Excel, please do as below:(**Free Download Kutools for Excel Now!) **

1. Select a blank cell which will place the summed result, and click **Kutools** > **Formula Helper **> **Formula Helper** . See screenshot:

2. In the **Formula Helper** dialog, check **Filter** checkbox, then type "sum" into the textbox, all formulas about summing are list in the **Choose a formula** section, click at **Sum absolute values**, then go to **Argument input** section to click to select the list of values you want to sum their absolute value, and then click **Ok**. See screenshot:

Now it has sum up the absolute values of the list right now.

#### **Sum Absolute Value In A Range**

**Related articles:**

### The Best Office Productivity Tools

#### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

**Reuse**: Quickly insert**complex formulas, charts**and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!