Sometimes it becomes a necessity for us to know the sum of some filtered cells. Though we can do it manually, Excel has some fantastic features to sum filtered cells. It will help a person when he has to handle a large amount of filtered data to sum. In this context, we will demonstrate to you 5 possible ways how to sum filtered cells in Excel. If you are interested to get yourself familiarized with those techniques, download our practice workbook and follow us.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

## 5 Easy Methods to Sum Filtered Cells in Excel

For explaining the methods, we consider a dataset of some fruits and their amounts. The name of those fruits is in **column B**, titled **Fruit Name** and their quantity is in column **C**, entitled **Amount(KG)**. So, we can say that our dataset is in the range of cells **B5:C14**. We are going to filter the dataset for **‘Apple’** and then sum up the quantity of this fruit.

### 1. Utilizing SUBTOTAL Function

In this method, we are going to use **the SUBTOTAL function** to sum filtered cells in Excel. Our dataset is in the range of cells **B5:C14**. The sum of the function will be in cell **C16**. We will filter the data for **‘Apple’** and sum up its quantity. The steps of this process are given below:

**📌 Steps:**

- First of all, select cell
**C16**. - Then, write down the following formula into the cell.

`=SUBTOTAL(9,C5:C14)`

- Here,
**9**is the function number of**the SUM function**. The values which the function will sum are in the range of cells**C5:C14**. - Press
**Enter**on your keyboard and you will get the sum of all rows in cell**C16**.

- Now, select the entire range of cells
**B4:C14**. - After that, in the
**Data**tab, select the**Filter**option from the**Sort & Filter**group.

- You will get 2 drop-down arrows that will come in the heading of our dataset.

- Click the drop-down arrow of the
**‘Fruit Name’**column. Uncheck the**Select All**option and click on**‘Apple’**only. - Finally, click
**OK.**

- You will see the dataset will be filtered only for the fruit
**Apple**and show the sum of its quantity.

Thus, we can say that our formula worked successfully and we are able to sum filtered cells in an Excel worksheet.

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

### 2. Sum Filtered Cells by Creating Table in Excel

Converting the entire range of the dataset into a table will also help us to display the sum of filtered cells. To show the approach, we will use the same dataset which we have used already in our previous method. Our dataset is in the range of cells **B5:C14**. The process is explained below step by step:

**📌 Steps: **

- At first, select the entire range of cells
**B4:C14**. - Now, in the
**Insert**tab, select**Table**from the**Tables**group. You can also press**‘Ctrl+T’**to create the table.

- A small dialog box entitled
**Create Table**will appear. - In this dialog box, click on
**My table had headers**and then, click**OK.**

- The table will be created. In the
**Table Design**tab, you can change the table name according to your desire from the**Properties**group.

- Then, from the
**Table Style Options**group, click on the**Total Row**. - You will see that a new row will appear below the table and show us the total value of column
**C**.

- After that, click on the drop-down arrow in the heading that shows
**Fruit Name**. - Uncheck the
**Select All**option and select the**Apple**option only. - Finally, click the
**OK**button to close that window.

- You will see only the rows containing the entity of
**Apple**remain in the dataset. Besides it, the row, titled**Total**, will show the sum of**Apple’s**quantity.

Finally, we can say that our method worked successfully and we are able to sum filtered cells in Excel.

**Read More:** **How to Sum Selected Cells in Excel (4 Easy Methods)**

### 3. Applying AGGREGATE Function

In this following procedure, we will use **the AGGREGATE function** to sum filtered cells in the Excel spreadsheet. Our dataset is in the range of cells **B5:C14**. The sum of the function will be in cell **C16**. We will filter the data for **Apple** and sum up its quantity. The steps of this method are given as follows:

**📌 Steps:**

- First, select cell
**C16**. - Now, write down the following formula into the cell.

`=AGGREGATE(9,5,B5:C14)`

- In this function, the first element,
**9**is the function number of**the SUM function**. The second element,**5**denotes to**‘ignore the hidden rows’**means the rows which we filter out or any hidden row’s value will not include in the calculation. The last element is the values that have to be sum is in the range of cells**C5:C14**. - Then, press
**Enter**key and you will get the sum of all rows in cell**C16**.

- After that, select the entire range of cells
**B4:C14**. - In the
**Data**tab, select the**Filter**option from the**Sort & Filter**group.

- You will see 2 drop-down arrows that will come in the heading of our dataset.
- Now, click the drop-down arrow of the
**Fruit Name**column. - Uncheck the
**Select All**option and click on the**Apple**option only.

- You will see the dataset will be filtered only for the fruit
**Apple**and show the sum of its quantity.

So, we can say that our formula worked perfectly and we are able to sum filtered cells in an Excel worksheet.

**Read More:** **How to Sum Only Visible Cells in Excel (4 Quick Ways)**

**Similar Readings**

**How to Sum by Group in Excel (4 Methods)****3 Easy Ways to Sum Top n Values in Excel****How to Sum Between Two Numbers Formula in Excel****Sum Cells in Excel: Continuous, Random, With Criteria, etc.****How to Sum Multiple Rows in Excel (4 Quick Ways)**

### 4. Using a Combined Formula to Sum Filtered Cells

In this procedure, we will use a formula that includes **SUMPRODUCT****, ****SUBTOTAL****, ****OFFSET****, ****MIN****,** and **ROW** functions to sum filtered cells in the Excel spreadsheet. Our dataset is in the range of cells **B5:C14**. Here, we have to write down the **Fruit Name** in cell **C16**, which cell is titled **Chosen Fruit**. The sum of the function is in cell **C17**. We will filter the data for **‘Apple’** and sum up its quantity. The steps of this method are given as follows:

**📌 Steps:**

- At the beginning of this method, select cell
**C17**. - After that, write down the following formula into the cell.

`=SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B14,ROW(B5:B14)-MIN(ROW(B5:B14)),,1)),( B5:B14=C16)*(C5:C14))`

- Press
**Enter**key on your keyboard.

- The result will be
**0**as we don’t write the**Chosen Fruit**name in cell**C16**. Now, in cell**C16**, write down your desired fruit name manually. In our case, we choose**Apple**to filter the sum. - Press the
**Enter**. - Finally, you will see in cell
**C17**the formula showing the sum of**Apple’s**quantity.

Thus, we can say that our formula worked accurately and we are able to sum filtered cells in an Excel worksheet.

**🔍 Breakdown of the Formula:**

We are doing this formula breakdown for cell **C17**

`👉`

**ROW(B5:B14):** This function returns simply the row number which contains our data.

`👉`

**MIN(ROW(B5:B14)):** This function returns the lowest row number of our dataset.

`👉`

**OFFSET(B5:B14,ROW(B5:B14)-MIN(ROW(B5:B14)),,1):** This function returns the difference between the row number and min row number to the **SUBTOTAL** function.

`👉`

**SUBTOTAL(3,OFFSET(B5:B14,ROW(B5:B14)-MIN(ROW(B5:B14)),,1))*(B5:B14=C16)*(C5:C14): **This function returns the value of quantity for **Apple** entities and 0 for **All Other** entities.

`👉`

**SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B14,ROW(B5:B14)-MIN(ROW(B5:B14)),,1)),( B5:B14=C16)*(C5:C14)):** This function returns 7000, the sum of all Apple quantity.

**Read More: ****[Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)**

### 5. Embedding VBA Code

Writing a VBA code will also help you to sum filtered cells in Excel. Our dataset is in the range of cells **B5:C14**. The sum of the function will be in cell **C16**. We will filter the data for **‘Apple’** and sum up its quantity. The method is described below step by step:

**📌 Steps:**

- To start the approach, go to the
**Developer**tab and click on**Visual Basic.**If you don’t have that, you have to**enable the Developer tab****.**Or You can also press**‘Alt+F11’**for opening the**Visual Basic Editor**.

- A dialog box will appear.
- Now, in the
**Insert**tab on that box, click**Module**.

- Then, write down the following visual code in that empty editor box.

```
Function Sum_Filtered_Cells(WorkRng As Range) As Double
Dim work_rng As Range
Dim output As Double
For Each work_rng In WorkRng
If work_rng.Rows.Hidden = False And work_rng.Columns.Hidden = False Then
output = output + work_rng.Value
End If
Next
Sum_Filtered_Cells = output
End Function
```

- Close the
**Editor**tab. - After that, in cell
**C16**, write down the following formula-

`=Sum_Filtered_Cells(C5:C14)`

- Press the
**Enter**key. - You will get the sum of all rows in cell
**C16**.

- Then, select the entire range of cell
**B4:C14**. - In the
**Data**tab, select the**Filter**option from the**Sort & Filter**group.

- You will see 2 drop-down arrows that will come in the heading of our dataset.
- After that, click the drop-down arrow of the
**Fruit Name**column. - Uncheck the
**Select All**option and click on the**Apple**option only.

- In the end, you will see the dataset will be filtered only for the fruit
**Apple**and show the sum of its quantity.

Lastly, we can say that our visual code worked successfully and we are able to sum filtered cells in an Excel spreadsheet

## Conclusion

That’s the end of this content. I hope that this will be helpful for you and you will be able to sum filtered cells in Excel. If you have any further queries or recommendations, please share them with us in the comments section below.

Don’t forget to check our website **ExcelDemy** for several Excel-related problems and solutions. Keep learning new methods and keep growing!