How to Sum only visible cells in Excel when using Filter

Posted June 3, 2018

A common question I get asked when I'm teaching people how to filter data is "how do I sum only the visible rows when filtering data in Excel"?

It's very common for people to try creating a SUM total at the bottom of a list and then filter the data hoping that the total will change to reflect the SUM of the data displayed. However, the total doesn't update. It still shows the SUM of the entire list.

You can definitely use the AutoSum button to instantly create a total that will update as you filter your data. Here's how you do it.

Sum only the visible rows

The first thing you will need to do is apply Filter to your data and then be sure to have the data filtered BEFORE trying to SUM the range. If you filter after applying the SUM function you will still see the total including the data hidden by the filter.

1. Select the data to be filtered and then on the Data tab click Filter. Use the filter arrows to filter the data. Do this prior to inserting the SUM function.

count filtered rows in excel

2. Now select the cell in which you want the SUM to be shown.

excel count filtered rows

3. From the Home tab click the AutoSum button, or press ALT + = which is the keyboard shortcut for SUM.

filter formula excel

4. Select over the filtered range that you want to SUM and then press Enter.

filter formula in excel

5. A SUBTOTAL function will be created for the selected range. The SUBTOTAL function will update the total to display the SUM of only the visible cells.

filter data in excel

In the example above the SUBTOTAL function has been applied to total the Total Sales list.

Be sure to check that the range in the SUBTOTAL function includes all cells required.

The function number ‘9’ is important as Excel uses this to determine the function used to create the subtotal result. The number 9 will create a SUM subtotal at each change in the data.

Use the table below to select the type of subtotal required.

FUNCTION NUMBER

FUNCTION NAME

1

AVERAGE

2

COUNT

3

COUNTA

4

MAX

5

MIN

9

SUM


Exclude data in hidden rows

If the range includes hidden rows these will be included in the subtotal.

To exclude data in hidden rows use the function number plus 100, e.g. 109 will produce a SUM excluding hidden rows.

When your total returns an error

If your total returns an error you may need to fix something within the data. Check our my post 5 Reasons why your Excel filter may not be working.

Using Excel Tables to subtotal your data

Excel Tables provide another brilliant way to subtotal your data. To learn more about them check out my post on how to use Excel Tables.

Was this blog helpful? Let us know in the Comments below.

If you enjoyed this post check out the related posts below.

Elevate your Excel game and become a pro with our exclusive Insider Group

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus, your list of Essential Excel Skills to become proficient in Excel!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>