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

We use the filter function quite often because of its ability to churn up the most important insights of the data, which helps us immensely to make decisions using data. This article tries to answer how to sum columns in excel when filtered most efficiently and simplistically.


Download Practice Workbook

Download this practice workbook below.


4 Ways to Sum Columns in Excel When Filtered

In this article, I am going to use this dataset for demonstrating purposes. We have Components, Manufacturer, Country of Manufacture, Quantity, Unit Price, and Total Price as the column header. We will try to filter these prices based on various criteria and those processes will be explained with broad demonstrations.

4 Ways to Sum Columns in Excel When Filtered


1. Using SUBTOTAL to Sum Columns When Filtered

The SUBTOTAL function is the most common way to calculate the sum of columns dynamically. It is done through Ribbons and formulas.


1.1 SUBTOTAL from AutoSum Option

In this method, the SUBTOTAL method will be applied through the AutoSum Option in the Editing group.

Steps

  • First, you need to make a table and apply AutoSum to it. For this, go to Data > Filter.

  • After this, you will notice that the regular filter icon on every column header appears.

SUBTOTAL from AutoSum Option

  • Then we will try to filter the table by Country of Manufacture. To do this click on the arrow sign on the corner of the table header in cell D4.

SUBTOTAL from AutoSum Option

  • After clicking the icon, check only the China option in the Text Filter option box, to show only the entries that belong to China. After that click OK.

SUBTOTAL from AutoSum Option

  • Then you will notice that the table now shows only the entries which belong to China in the Country of Manufacture column.

SUBTOTAL from AutoSum Option

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

SUBTOTAL from AutoSum Option

  • After that you will see the SUBTOTAL function showing at the cell G17, you need to select the data arrays in the Total Prize column and press Enter.

SUBTOTAL from AutoSum Option

  • After pressing enter you will notice your total summation of the filtered data is now showing properly. They also matched with the SUM preview below.

SUBTOTAL from AutoSum Option


1.2 Utilizing SUBTOTAL Function

Using the SUBTOTAL function, we can easily calculate the sum of column values after filtering is done.

Steps

  • First of all, select the whole data set and press Ctrl+T. It will turn the selected dataset into an Excel table.

  • After that, a new window will create, and inside of that table, you need to select the range of your dataset. Make sure to tick the My table has headers. Click OK after this.

Using SUBTOTAL Function

  • After clicking OK, you will observe that your data set is now converted into a table.
  • Next enter the following formula in to the cell G16:
=SUBTOTAL(9,G5:G15)

  • After entering the formula, you will notice that 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.
  • Then select Japan by checking the box and then click OK.

Using SUBTOTAL Function

  • After clicking OK, you will notice that your summation value at cell G16 is now updated for filtered value.

Read More: How to Sum Entire Column in Excel (9 Easy Ways)


2. Use of Total Row in Excel Table to Sum Filtered Columns

Utilizing the table row property of Excel tables you can calculate the sum of filtered cells quite easily.

Steps

  • First of all, select the whole data set and press ‘Ctrl+T’. It will turn the selected dataset into an Excel table.

Use of Total Row in Excel Table

  • After that, a new window will create, and inside of that table, you need to select the range of your dataset. Make sure to tick the My table has headers. Click OK after this.

  • After clicking OK, you will observe that your dataset is now converted into a table.
  • Now go to the Table Design > Table Style Options. Then check the Total Row box.
  • Next, you will observe a row below the existing dataset created, Total in cell B16, and a new dropdown menu at cell G16. From the dropdown menu select SUM and then you will see the total sum of the Total Price column.

Use of Total Row in Excel Table

  • Now if you select the drop-down sign in the corner of the Country of Manufacture cell and choose China And click OK.

Use of Total Row in Excel Table

After clicking OK, you will notice that only China entries are filtered in, and the summation value is now updated for filtered entries.

Use of Total Row in Excel Table

Read More: How to Sum Columns in Excel Table (7 Methods)


Similar Readings

 


3. Applying AGGREGATE Function

The AGGREGATE function can get the sum value of columns after they are filtered out.

Steps

  • To understand why AGGREGATE functions are needed, we first demonstrate why SUM functions don’t work in traditional worksheets.
  • First make a table from you dataset that you created before, and from that filter choose entries from only Japan or the country of Manufacture columns.
  • Then enter the SUM function and select the Total Price column as an array argument.

Applying Aggregate Function Sum Columns When Filtered

  • Then you will notice that 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. Which is 11 value instead of filtered 4 value. It is evident as the value from SUM preview and summation of selected cells doesn’t match.

Applying Aggregate Function Sum Columns When Filtered

To counter this issue, using the AGGREGATE function could be helpful.

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

Applying Aggregate Function Sum Columns When Filtered

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

Applying Aggregate Function Sum Columns When Filtered

  • Finally, select the array of cells whose summation you need to get.

Applying Aggregate Function Sum Columns When Filtered

  • After that, you can see that the filtered cells’ SUM value matches perfectly with the SUM preview value shown below. This further confirms that this summation accurately calculates only entries from China.

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 (6 Easy Methods)


4. Emebbeding VBA Code to Sum Columns When Filtered

Using a simple VBA Macro can drastically reduce the time to Extract part of text from a long string.

Steps

  • First, go to the Developer tab, then click Visual Basic.

 

  • Then click Insert > Module.

Applying Aggregate Function Sum Columns When Filtered

 

  • 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

  • Then close the window.
  • After that select the whole window and press Ctrl+T.

Emebbeding VBA Code to Sum Columns

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

Emebbeding VBA Code to Sum Columns

  • Now the whole dataset is converted to table, enter the new formula just created through VBA in cell G16 :
=SumColumn([Total Price])

Emebbeding VBA Code to Sum Columns

  • After entering the data you will see the total value of prices listed in cell G16.
  • Now, click the filter arrow icon on the corner of the County of Manufacturing column and choose South Korea, Taiwan, and Vietnam. Click OK after that.

Emebbeding VBA Code to Sum Columns

  • After then you will see the updated sum with only the filtered cells shown which matched exactly with the SUM preview value.

Emebbeding VBA Code to Sum Columns

So, we can say, our method worked successfully to sum columns in Excel when filtered.

Read More: Sum Every nth Column in Excel(Formula and VBA Code)


Conclusion

To sum it up, the question “how to Sum ColumnS in Excel when filtered” is answered here in 3 different ways. Among them SUBTOTAL method is actually into 3 sub-methods and explained accordingly, continue to use Aggregate function, ended up with using VBA Macros. Among all of the methods used here, using the SUBTOTAL ribbon method is the easier to understand and simple one. The VBA process is also less time-consuming and simplistic but requires prior VBA-related knowledge. Other methods don’t have such a requirement.

For this problem, a macro-enable workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo