# 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.

## Question? Ask it in our Excel Forum

Excel Function: SUBTOTAL()

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 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()