How to Sum Visible Rows in a Filtered List

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 =SUBTOTAL(9,C2:C16) in the formula bar of G5.
  • 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 =SUBTOTAL(109,C2:C16) in the formula bar of G5.
  • 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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar