Go Back

# How to Sum Visible Rows in a Filtered List

We can sum only the VISIBLE rows (ROWS NOT FILTERED OUT) in a filtered list with the SUBTOTAL FUNCTION with function number 9 or 109. This function automatically disregards hidden rows in a filtered list or table. The steps below will walk through the process.

Figure 1: How to Sum Visible Rows in a Filtered List

## General Formula

`=SUBTOTAL(9,RANGE)`

## Formula

`=SUBTOTAL(9,C4:C13)`

## Setting up the Data

• We will set up data by inputting items sold in Cell B4 to Cell B13
• The sales amount are in Cell  C4 to Cell C13
• Our result will be returned by the SUBTOTAL function in Cell C14

Figure 2: Setting up the Data

## Creating a Filtered List

Assuming we want to remove sales that are below \$500, we can create a FILTERED LIST like this:

• We will click on Cell C3, we will right click and click on filter. We will click on “filter by cell value.” The result is figure 3

Figure 3: Creating a Filtered List

• We will click on the icon in Cell C3
• A dialog will show up and we can select the values we are interested in. Note, we have to select blank as well for the sake of our result

Figure 4: Filtered List

• We will notice that Cell C6 with \$280 has been automatically hidden

## Using the SUBTOTAL Function

• We will click on Cell C14
• We will input the formula below
`=SUBTOTAL(9,C4:C13)`
• We will press the enter key

Figure 5: Result of the SUBTOTAL function

## Note

• If you want to MANUALLY HIDE ROWS (right-click, HIDE), then use this formula below:
`=SUBTOTAL(109,C4:C13)`
• By changing the function number, the SUBTOTAL function can do many other calculations

## Instant Connection to an Expert through our Excelchat Service

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.