# Sum the Visible Rows from a Filtered List in Excel

Author:

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. Result: If I remove or change the filter, watch how the total will update:  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

Excel Function: SUBTOTAL()

#### Question? Ask it in our Excel Forum ##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

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 ...
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 ...
Count the Visible Rows in a Filtered List in Excel
Tutorial: How to use the COUNT or COUNTA function on a filtered list of data so that hidden rows ar...
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 data ...
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...
Tutorial Details
Excel Function: SUBTOTAL() 