Sum the Visible Rows from a Filtered List in Excel

Add to Favorites

How to SUM only the visible rows from a filtered data set in Excel.

To do this, we will use the SUBTOTAL() function.

Sections:

Syntax

Example - Filtered Data

Example - Exclude Manually Hidden Rows

Notes

Syntax

=SUBTOTAL(9, range_to_sum)

9 tells the function to perform a sum.

range_to_sum is the range that you want to sum.

Example - Filtered Data

=SUBTOTAL(9,B4:B8)

This sums all visible values from a filtered list or table for the range B4:B8.

4ee03debc1921626268fd483b0a77922.png

Result:

824be0426fac4c9090525736764d5655.png

If I remove or change the filter, watch how the total will update:

7ebfb608ca4f93cea59933d2ef8b16c5.png

63a7c40ea632ed25f9a76c6c01496ec9.png

This is the beauty of this function; it works with your filtered data sets.

Example - Exclude Manually Hidden Rows

The above function only works on data that is being filtered. It does NOT work on rows that are manually hidden (right-click a row and then click Hide).

To exclude data that is also manually hidden, we change the function to this:

=SUBTOTAL(109,B4:B8)

109 this was a 9 in the first example. Changing this to 109 means that rows that are hidden via a filter and also via manually hiding the row will now not show up in the sum.

B4:B8 this is still just the range to sum.

Notes

The SUBTOTAL function allows you to apply a number of regular functions to filtered data, for a full list and explanation, view our tutorial on the SUBTOTAL function in Excel.

Make sure to download the file for this tutorial so you can work with the above examples in Excel.


Excel Function: SUBTOTAL()
Downloadable Files: Excel File

Similar Content on TeachExcel
Average the Visible Rows in a Filtered List in Excel
Tutorial: Average the results from a filtered list in Excel. This method averages only the visible ...
Count the Visible Rows in a Filtered List in Excel
Tutorial: How to use the COUNT function on a filtered list of data so that hidden rows are not incl...
MAX and MIN Values from a Filtered List in Excel
Tutorial: How to get the MAX and MIN values from a filtered data set. This method returns the value ...
Filter Data to Show the Top 10 Items from a Data Set in Excel - AutoFilter
Macro: This Excel macro filters data in Excel in order to display the top 10 items from the d...
Extract a Word from a Sentence / Cell in Excel with this UDF - Allows for a User-Defined Delimiter
Macro: Extract whole words from a cell or sentence in Excel with this UDF. This allows you to spe...
SUBTOTAL Function - Work on Filtered Data in Excel
Tutorial: Perform basic functions on a filtered dataset in excel, including SUM, AVERAGE, COUNT, COU...