When working with numbers and large datasets in Excel, sometimes we need to find the top numbers from the dataset and sum the values. The sales type dataset needs this most frequently. In this article, I will show you 3 suitable ways to sum any top n values from a data set in Microsoft Excel.

## Download Practice Workbook

You can download our practice workbook from here for free!

## 3 Methods to Sum Top n Values in Excel

First of all, let us have a look at our data set. We have a sales record of two months for a company named Nicholas Group.

Now we will try to sum any top n values from the sales.

First, let’s try to sum up the top 5 sales from January.

### 1. Combine LARGE Function with SUMIF to Sum Top n Values in Excel

We can accomplish the targetted task by using the **SUMIF function** of Excel along with the **LARGE** **function**. Follow the steps below to do this.

**📌**** Steps:**

- First, click on
**cell F5**. - Afterward, insert the following formula and press the
**Enter**key.

`=SUMIF(C5:C17,">="&LARGE(C5:C17,5))`

**🔎**** Formula Breakdown:**

*LARGE(C5:C17,5)*

It returns the 5th largest value from the cells **C5** to **C17**.

**Result:** $25000.00

*SUMIF(C5:C17,”>=”&LARGE(C5:C17,5))*

It returns the sum of the cells from **C5** to **C17** which contain values greater than or equal to the previous result.

**Result:** 150,000.00

Thus, you will get the sum of the top 5 sales from January, which is $150,000.00.

**Read More:** **Excel Sum If a Cell Contains Criteria (5 Examples)**

### 2. Use SUM Formulas to Sum Top n Values in Excel

We can also calculate the sum of the top 5 sales for the month of January using the **SUM** function of Excel.

#### 2.1 Combine SUM, IF, and RANK Functions to Sum First n Numbers

You can execute the task using the **SUM**, **IF, **and **RANK** functions of Excel. Follow the steps below to do this.

**📌**** Steps:**

- First and foremost, click on
**cell F5**and insert the following formula.

`=SUM(IF(RANK(C5:C17,C5:C17)<=5,C5:C17,0))`

**🔎**** Formula Breakdown:**

*IF(RANK(C5:C17,C5:C17)<=5,C5:C17,0)*

It takes an array of criteria **(RANK(C5:C17,C5:C17)<=5)** in place of a single criterion, and it returns a TRUE for each cell between **C5** to **C17**, if it has a value in the top 5 zones. Otherwise, it returns a FALSE. When TRUE, it would return the corresponding cell value from C5 to C17, and when FALSE, it would return 0.

**Result:** **{0,0,0,0,0,25000,28000,30000,0,0,0,35000,32000}**

*SUM(IF(RANK(C5:C17,C5:C17)<=5,C5:C17,0))*

It sums up the values of the resultant array.

**Result:** $150,000.00

- Subsequently, press the
**Ctrl + Shift + Enter**key, as this is an array formula.

As a result, we will get the same result as earlier, $150,000.00

**Read More:** **How to Add Multiple Cells in Excel (6 Methods)**

#### 2.2 Combine SUM with LARGE Function

You can calculate the sum of the top 5 sales using the **SUM** function with the **LARGE** function of Excel. Go through the steps below to do this.

**📌**** Steps:**

- At the very beginning, click on
**cell F5**. - Subsequently, insert the following formula and press
**Ctrl + Shift + Enter**key.

`=SUM(LARGE(C5:C17,{1,2,3,4,5}))`

**🔎**** Formula Breakdown:**

*LARGE(C5:C17,{1,2,3,4,5})*

It takes an array of values **{1,2,3,4,5} **in place of a single value k. And returns an array containing the 1st, 2nd, 3rd, 4th, and 5th largest values from the **C5:C17** range.

**Result: **{35000,32000,30000,28000,25000}

*SUM(LARGE(C5:C17,{1,2,3,4,5}))*

It sums up the previous resultant values.

**Result: **$150,000.00

Consequently, we will get the sum of the top 5 sales from January, which is $150,000.00.

#### 2.3 Combine SUM with SEQUENCE Function

Now, think for a moment. You have to calculate the sum of the top 100 values from a data set. Is it possible to enter manually 1,2,3… up to 100 in the previous formula? That is almost impossible. So what is the way?

No worries. We can still accomplish it using the **SUM** and the **LARGE** function of Excel, but this time using one more function, **SEQUENCE**. Let’s try to find out the sum of the top 10 sales from January this time. Follow the steps below to achieve this target.

**📌**** Steps:**

- First, click on
**cell F5**. - Following, insert the formula below and hit the
**Enter**key.

`=SUM(LARGE(C5:C17,SEQUENCE(10,1)))`

**🔎**** Formula Breakdown:**

*SEQUENCE(10,1)*

It returns an array of values from 1 to 10.

**Result:** {1,2,3,4,5,6,7,8,9,10}.

*LARGE(C5:C17,SEQUENCE(10,1))*

It returns the top 10 large sales in the range of **C5** to **C17.
Result: **{35000,32000,30000,28000,25000,24500,24000,23000,22000,21000}

*SUM(LARGE(C5:C17,SEQUENCE(10,1)))*

It sums the previous resultant array.

**Result: **$264,500.00

Thus, we will get the sum of the top 10 sales of January, which is $264,500.00.

**Note**:

The **SEQUENCE** function is only available in **Office 365**.

**Read More:** **How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)**

**Similar Readings**

**How to Add Numbers in Excel (2 Easy Ways)****All the Easy Ways to Add up (Sum) a column in Excel****How to Sum Multiple Rows and Columns in Excel****[Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)****How to Sum Filtered Cells in Excel (5 Suitable Ways)**

### 3. Use SUMPRODUCT Formulas to Sum Top n Values in Excel

You can also use the **SUMPRODUCT function** to sum top n values in Excel by following any of the options below.

#### 3.1 SUMPRODUCT with LARGE, ROW, and INDIRECT Functions

In Option 2 from section 2, we saw how you can calculate the sum of the top 5 sales using the **LARGE** and the **SUM** functions. It is easy for the top 5, but when it comes to large numbers like the top 50 or the top 100, this process is quite unuseful.

We achieved the same goal in option 3 of section 2 for top 10 sales, using a combination of **SUM,** **LARGE,** and **SEQUENCE** functions. But the **SEQUENCE** function is only available in **Office 365**.

Now, we will again achieve the same goal, but this time using a combination of **SUMPRODUCT,** **LARGE, ROW,** and **INDIRECT** functions. Follow the steps below to accomplish this.

**📌**** Steps:**

- Initially, click on
**cell F5**. - Subsequently, insert the formula below and press the
**Enter**key.

`=SUMPRODUCT(LARGE(C5:C17,ROW(INDIRECT("1:10"))))`

**🔎**** Formula Breakdown:**

*ROW(INDIRECT(“1:10”))*

It returns an array of values from 1 to 10.

**Result: **{1,2,3,4,5,6,7,8,9,10}.

*LARGE(C5:C17,ROW(INDIRECT(“1:10”)))*

It returns the top 10 large values in range C5 to C17.

**Result:** {35000,32000,30000,28000,25000,24500,24000,23000,22000,21000}

*SUMPRODUCT(LARGE(C5:C17,ROW(INDIRECT(“1:10”))))*

It returns the sum of the top 10 large values.

**Result:** $264,500.00

Thus, we will get the same result for the top 10 sales of January as earlier, $264,500.00.

**Read More:** **How to Add Rows in Excel with Formula (5 ways)**

#### 3.2 SUMPRODUCT with RANK Function

Let’s try to find out the sum of the top 10 sales again, this time using **SUMPRODUCT** along with the **RANK function** of Excel. Follow the steps below to do this.

**📌**** Steps:**

- First and foremost, click on
**cell F5**and insert the following formula.

`=SUMPRODUCT(C5:C17,--(RANK(C5:C17,C5:C17)<=10))`

- Subsequently, press the
**Enter**key.

**🔎**** Formula Breakdown:**

*–(RANK(C5:C17,C5:C17)<=10)*

It returns an array of **TRUE** or **FALSE**. For each cell in the range **C5** to **C17** which falls under the top 10 it returns a **TRUE**, and **FALSE** for the rest.**‘–‘** converts the TRUE and FALSE array into an array of 1 and 0.

**Result: **{0,0,1,1,0,1,1,1,1,1,1,1,1}

*SUMPRODUCT(C5:C17,–(RANK(C5:C17,C5:C17)<=10))*

It multiplies C5:C17 cell values to the previous resultant array. Therefore, it returns the sum of the top 10 sales.

**Result: **$264,500.00.

As a result, we will get the sum of the top 10 sales values from January.

**Read More:** **Excel Sum Last 5 Values in Row (Formula + VBA Code)**

## How to Sum Top n Values in Excel with Criteria

Let’s try something different now. Try to find out the sum of any top n values, but with a criterion. Let’s try to calculate the sum of the top 10 sales in January, but we will consider only the sales which are less than $30,000.00.

You can do this using the **SUMPRODUCT** function just like in the previous example. Follow the steps below to achieve the result.

**📌**** Steps:**

- At the very beginning, click on
**cell F5**. - Following, insert the formula below.

`=SUMPRODUCT(C5:C17,(--(RANK(C5:C17,C5:C17)<=10))*(--(C5:C17<30000)))`

- Subsequently, hit the
**Enter**key.

Thus, you will get the sum of the top 10 sales in January less than $30,000.00 is $167,500.00 as your desired result.

**Note: **

Here **C5:C17** is the range of values from which I extracted the top 10, you use your one.

And if you want, you can also use the more complicated formula below:

`=SUMPRODUCT(((LARGE(C4:C18,ROW(INDIRECT("1:10"))))<30000)*LARGE(C4:C18,ROW(INDIRECT("1:10"))))`

**Read More:** **Sum Cells in Excel: Continuous, Random, With Criteria, etc.**

## How to Sum Top n Values in Excel with Texts Inside

Now, come to February. Let’s try to find out the top 10 sales from February. Obviously, we can use any formula from the earlier sections. But one problem is that, in a few cells in the February column, there is a text **“No Sales”**. And when we use the formulas, it will show errors. So, what to do?

But, no worries. Just wrap the **LARGE** portion from any of the above formulas within an **IFERROR **function. To make it clear, follow the steps below to achieve your desired target in this way.

**📌**** Steps:**

- First and foremost, click on
**cell F5**and insert the following formula.

`=SUM(IFERROR(LARGE(D5:D17,ROW(INDIRECT("1:10"))),0))`

- Subsequently, press the
**Ctrl + Shift + Enter**key.

Thus, the error because of text calculation will be ignored and you will get the top 10 sales from February would be $261,000.00.

**Note:**

You can use any other formula from section 2, but just wrap the **LARGE** portion within an **IFERROR** function.

**Read More:** **How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)**

**Conclusion**

So, in this article, I have shown you 3 suitable ways to sum top n values in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.

And, visit **ExcelDemy** to learn about many more Excel problem solutions, tips, and tricks. Thank you!

**Further Readings**

**How to Sum Selected Cells in Excel (4 Easy Methods)****Sum If a Cell Contains Text in Excel (6 Suitable Formulas)****How to Sum Only Visible Cells in Excel (4 Quick Ways)****Sum Only Positive Numbers in Excel (4 Simple Ways)****How to Calculate Cumulative Sum in Excel (9 Methods)****Sum Between Two Numbers Formula in Excel****How to Sum If Cell Contains Specific Text in Excel (6 Ways)**