How to Sum Filtered Cells in Excel (5 Suitable Ways)

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.

Using SUBTOTAL Function to Sum Filtered Cells in Excel

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

Using SUBTOTAL Function to Sum Filtered Cells in Excel

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

Using SUBTOTAL Function to Sum Filtered Cells in Excel

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

Using SUBTOTAL Function to Sum Filtered Cells in Excel

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.

Using Total Row to Sum Filtered Cells

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

Using Total Row to Sum Filtered Cells

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

Using Total Row to Sum Filtered Cells

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

Using Total Row to Sum Filtered Cells

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

Using Total Row to Sum Filtered Cells

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.

Applying AGGREGATE Function to Sum Filtered Cells in Excel

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

Applying AGGREGATE Function to Sum Filtered Cells in Excel

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

Applying AGGREGATE Function to Sum Filtered Cells in Excel

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

Applying AGGREGATE Function to Sum Filtered Cells in Excel

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


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.

Using Formula to Sum Filtered Cells

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

Using Formula to Sum Filtered Cells

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.

Embedding VBA Code

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

Embedding VBA Code

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

Embedding VBA Code to Sum Filtered Cells in Excel

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

Embedding VBA Code to Sum Filtered Cells in Excel

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

Embedding VBA Code to Sum Filtered Cells in Excel

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!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo