Learn Excel Now

Conquer the Fear of Excel

  • Home
  • Training
    • Excel
    • MS Office
    • Outlook
    • PowerPoint
    • Word
    • OneDrive
    • Google
  • All-Access
  • eLearning
    • Excel Foundations
    • Micro Courses
      • Simply Excel
  • Resources
  • Login

How to Calculate a Subtotal In Excel Using the Filter

July 20, 2016 by Tyrone Pernsley

Excel has many ways to calculate data. If you know the right formulas and functions, you can find out just about anything you want to know about your data. Today’s lesson is on using the subtotal formula to find various totals based on the filter option in Excel.

Imagine you are running sales numbers using the following the spreadsheet:

Calculating Subtotals Image 1

If you remember from last week’s blog post on Sorting and Filtering data, we have gone ahead and added the filter. Now, if you wanted to find the sales total, you can use the following formula:

=sum(range)

This is the standard way to find a total. But, as you can see, once you use this formula and change the anything from the filtered drop-down menu, the sales total doesn’t change with it:

Calculating Subtotals Gif 1

So, how do you get the total to change with changes you make on the filter? This is where you will use the Subtotal formula. The Subtotal formula is:

=subtotal(function_number, ref1…)

Note: You need to select which function you want the subtotal to use. For finding sums, we use function 9-Sum. As you can see from the following menu, there are multiple functions to choose from:

Subtotal Menu Options

Once the function is selected it’s time to enter the range. For the spreadsheet example, we are looking for subtotals on the Sales column, Column D. So, the final formula looks like this:

=subtotal(9,D2:D21)

When you first enter this formula in cell D22, it gives you the total amount, the same as when you enter the Sum formula. However, watch what happens when you change the options using the Filter drop-down:

How to Calculate a Subtotal In Excel Using the Filter

As you can see, the Subtotal formula lets you find totals by Salesperson, Client, Product, etc. quickly and easily. And the total changes with the information you select.

We here at Learn Excel Now hope you found today’s tip on how to calculate a subtotal useful.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

All Access Subscription
Our 1-year all-access subscription provides step-by-step guidance for mastering Microsoft Office applications, with workshops, exercises and quick reference eGuides. It's perfect for anyone that needs to brush up on certain skills or even folks who need to learn the programs - from a beginner’s level.
Excel Foundations
Take the fear out of Excel with this 20 module self-paced training course. You will cover the most essential topics to develop a solid foundation of Excel or a lifetime of mastery.

Learn Excel Now helps you conquer the fear of Excel. By providing self-paced and instructor-led training and free strategies and guides, we leave no Excel mystery unsolved.

  • Home
  • Training
  • All-Access
  • Excel Foundations
  • Resources

Connect with Us

  • Facebook
  • Linkedin

Contact

Learn Excel Now
questions@learnexcelnow.com
1-484-259-7664 or 1-800-964-6033
660 American Ave
Suite 203
King of Prussia, PA 19406

Microsoft® Office Excel® is a registered trademark of the Microsoft Corporation in the United States and other countries. All rights reserved.

Copyright © 2023 ­Learn Excel Now

Terms of Service

Privacy Policy

Copyright © 2023 · News Pro Theme on Genesis Framework · WordPress · Log in

WELCOME BACK!

Enter your username and password below to log in

Forget Your Username or Password?

Reset Password

Lost your password? Please enter your username or email address. You will receive a link to create a new password via email.

Log In