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.

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.

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.