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.
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!
