# How to Sum Columns in Excel When Filtered (7 Ways)

Consider the following dataset for demonstrating how to sum columns. We have Components, Manufacturer, Country of Manufacture, Quantity, Unit Price, and Total Prices. We will filter these prices based on various criteria.

## Method 1 – Using the SUBTOTAL Function to Sum Columns in Excel When Filtered

### Case 1.1 – Inserting SUBTOTAL from the AutoSum Option

Steps

• Make a table and apply AutoSum to it.
• Go to Data and Filter.

• The regular filter icon on every column header appears.

• We will filter the table by Country of Manufacture. Click on the arrow sign on the corner of the table header in cell D4.

• Check only the China option in the Text Filters option box to show only the entries that belong to China.
• Click OK.

• The table now shows only the entries that belong to China in the Country of Manufacture column.

• Select cell G17, and then from the Home tab, go to Editing group and click on the AutoSum option.

• You will see the SUBTOTAL function showing in cell G17.
• Select the data arrays in the Total Prize column and press Enter.

• The total summation of the filtered data is now showing properly. It matches with the SUM preview below.

### Case 1.2 – Applying the Excel SUBTOTAL Function

Steps

• Select the whole data set and press Ctrl + T. It will turn the selected dataset into an Excel table.

• A new window will be created. Select the range of your dataset.
• Make sure to tick the My table has headers.
• ClickÂ OK.

• Your data set is now converted into a table.
• Enter the following formula into the cell G16:
`=SUBTOTAL(9,G5:G15)`

• The value of summation from the range of cells G5:G15 is now showing in cell G16.
• You can now filter the Country of Manufacture by clicking the corner box on cell D4.
• Select Japan by checking the box, and then click OK.

• After clicking OK, the summation value at cell G16 is now updated for filtered value.

Read More: How to Sum Entire Column in ExcelÂ

## Method 2 – Calculating the Total with an Excel Table to Sum Filtered Columns

Steps

• Select the whole data set and press Ctrl + T. It will turn the selected dataset into an Excel table.

• A new window will be created. Select the range of your dataset if it’s not imported already.
• Make sure to tick the My table has headers.
• Click OK.

• The dataset is now converted into a table.
• Go to Table Design and Table Style Options.
• Check the Total Row box.
• A row below the existing dataset names Total is in cell B16 and a new dropdown menu at cell G16.
• From the dropdown menu, select SUM, and you’ll get the total sum of the Total Price column.

• Select the drop-down sign in the corner of the Country of Manufacture cell and choose China.
• Click OK.

• Only China entries are filtered in, and the summation value is now updated for filtered entries.

## Method 3 – Inserting the AGGREGATE Function to Sum Columns

Steps

• To understand why AGGREGATE functions are needed, we first demonstrate why SUM functions donâ€™t work in traditional worksheets.
• Make a table from your dataset that you created before, and filter entries from Japan.
• Enter the SUM function and select the Total Price column as an array argument.

• The summation we got is not actually the summation of filtered cells. Instead, it takes all the cell values from the range of cells G5:G15. The value from the SUM preview and summation of selected cells doesnâ€™t match.

• Enter the AGGREGATE function in cell G16 after filtering out the desirable value, in this case, China.
• The first argument should be 9, or select SUM from the drop-down menu.

• Type 5 or select Ignore hidden rows values from the drop-down menu.

• Select the array of cells which you need to sum.

• The filtered cells’ SUM value matches perfectly with the SUM preview value shown below.

Note:

1. This method only works after you filter out data according to your criteria. If you change your data filter, then the summation will not also change. You need to input formulas again in the cells.

2. The AGGREGATE function also doesnâ€™t work for hidden columns.

Read More: How to Sum Columns by Color in ExcelÂ

## Method 4 – Applying VBA Macro to Sum Columns When Filtered

Steps

• Go to the Developer tab, then click Visual Basic.

• Click Insert and Module.

• In the module window, enter the following code:
``````Function SumColumn(Wr As range) As Double
Dim y As range
Dim x As Double
For Each y In Wr
If y.Rows.Hidden = False And y.Columns.Hidden = False Then
x = x + y.Value
End If
Next
SumColumn = x
End Function``````

• Close the window.
• Select the whole sheet and press Ctrl + T.

• A new small window will open asking for the range of the table.
• Select the range and check that My table has headers box.

• The whole dataset is converted to a table.
• Enter the new formula just created through VBA in cell G16:
`=SumColumn([Total Price])`

• The total value of prices is listed in cell G16.
• Click the filter arrow icon on the corner of the County of Manufacturing column and choose South Korea, Taiwan, and Vietnam.
• Click OK.

• You will see the updated sum with only the filtered cells shown which matches exactly with the SUM preview value.

Read More: How to Sum Every Nth Column in Excel

## Related Articles

<< Go Back to Sum Columns |Â Sum in ExcelÂ

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF