One of the most helpful features of Excel is the ability to filter a list. However, when it comes to make calculations on that filtered list, many of us face difficulties. To sum visible rows in a filtered list, we can use the SUBTOTAL function with function number 9 or 109. In this tutorial, we will learn how to sum only visible rows in a filtered list in Excel.

*Figure 1. Example of How to Sum Visible Rows in a filtered list*

**Syntax**

**=SUBTOTAL(9, Range) or =SUBTOTAL(109, Range)**

- Range

It is the list or range that is filtered where we want to sum the visible rows only.

**How the Function Works**

The SUBTOTAL function returns the subtotal in a list or range. The function number 9 or 109 represents SUM. Here, we use 9 to sum the visible rows that are not filtered out. To sum the rows when we filter manually, we need to use it with the function number 109. SUBTOTAL ignores the hidden rows in a filtered table. It performs the calculations with the visible cells only.

**Setting Up Data**

The following example contains a car sales database for the last quarter of the year 2017. Column A has the makes, column B has the models and column C number of cars sold during the quarter.

* Figure 2. The Sample Data*

**Sum Visible Rows that is Filtered by Filters**

Filtering the list so it shows only BMW cars, we can calculate the total revenue. To do this:

- We need to go to cell
**G5**. - Assign the formula

in the formula bar of G5.**=SUBTOTAL(9,C2:C16)** - Press
**Enter**.

* Figure 3. Assigning the Formula to Sum Visible Rows in a Filtered List*

This will show the total sales for BMW for the last quarter which is 783

**Sum Visible Rows in a List that is Filtered Manually**

Filtering the list in the previous by hiding the models other than Honda, we end up with the list below. To find the total revenue for Honda:

- We need to go to cell
**G5**. - Assign the formula
`=`

in the formula bar of G5.**SUBTOTAL(109,C2:C16)** - Press Enter.

* Figure 4. Assigning the Formula to Sum Visible Rows Filtered Manually*

This will show the total sales for Honda, which is 1585.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment