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.
2. Now select the cell in which you want the SUM to be shown.
3. From the Home tab click the AutoSum button, or press ALT + = which is the keyboard shortcut for SUM.
4. Select over the filtered range that you want to SUM and then press ENTER.
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.
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.
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.
She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.
If you enjoyed this you may also enjoy...
How to freeze a row in Excel
Let’s look at how to freeze a row in Excel. It can be a real pain when you don’t have freeze panes on, and when you start to scroll down the screen you lose the headings at the top of your columns.
Use Conditional Formatting to identify cells that are of interest
Learn how to use Conditional Formatting to identify cells that are of interest. For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using
Find, modify and break links to an Excel workbook
Learn how to find, modify and break links to an Excel workbook. Linking is great, until you no longer need it. Then it can be a bit of a pain. A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data
Link cells in the same or different Excel worksheets
Learn how to link Link cells in the same or different Excel worksheets. Linking saves a huge amount of time (and a huge amount of mistakes) in that it allows you to create connections from one cell to another.
Use the SUMIF function to total only the cells that match your requirement
Use the SUMIF function to total only the cells that match your requirement. For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a
Insert an Excel function into your workbook
Learn how to insert an Excel function into your workbook. Excel comes equipped with a large number of pre-defined formulas. These are known as functions. There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few.