Skip to main content

How to subtotal only the visible cells after filtering in Excel?

Author: Siluvia Last Modified: 2019-11-01

Supposing you have calculated the total value from a column with the Sum function. When filtering data in the column, the Sum function adds both the visible cells and the hidden cells. If you only want to subtotal the visible cells after filtering, methods in this tutorial will help you.

Subtotal only visible cells after filtering with the Subtotal function
Subtotal only visible cells after filtering with an amazing tool


Subtotal only visible cells after filtering with the Subtotal function

Actually, the Subtotal function can help you to sum only the visible cells after filtering in Excel. Please do as follows.

Syntax

=SUBTOTAL(function_num,ref1,[ref2],…)

Arguments

  • Funtion_num (Required): A number from 1 to 11 or 101 to 111 that specifies the function to use for the subtotal.
  • Ref1 (Required): A named range or reference you want to subtotal.
  • Ref2 (Optional): A named range or reference you want to subtotal.

1. Select a blank cell, copy the below formula into it and press the Enter key.

=SUBTOTAL(9,C2:C13)

Note:

  • In the formula, number 9 means that you specify the sum function to the subtotal; C2:C13 is the range you will subtotal.
  • You can change the number 9 to any number mentioned in the below table based on your needs.
Function_num
(includes manually hidden rows)
Function_num
(excludes manually hidden rows)
Function
 1  101  AVERAGE
 2  102  COUNT
 3  103  COUNTA
 4  104  MAX
 5  105  MIN
 6  106  PRODUCT
 7  107  STDEV
 8  108  STDEVP
 9  109  SUM
 10  110  VAR
 11  111  VARP

From now on, when you filter the column data, the SUBTOTAL function only sum the visible cells as the below screenshot shown.


Subtotal only visible cells after filtering with an amazing tool

Here recommend the SUMVISIBLE cells function of Kutools for Excel for you. With this function, you can easily sum only visible cells in a certain range with several clicks.

Before applying Kutools for Excel, please download and install it firstly.

1. Select a blank cell to output the result, click Kutools > Kutools Functions > Statistical & Math > SUMVISIBLE. See screenshot:

2. In the Function Arguments dialog, select the range you will subtotal and then click the OK button.

Then you can see a formula is created in the selected cell. When you filter the column data, only the visible cells are summed.

  If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.


Related articles

Only delete visible rows or columns in Excel
In Excel, we usually hide some important rows or columns of information, sometimes, we need to delete the visible rows or columns only, if you delete them with Delete key directly, the hidden values will be deleted at the same time. How do you delete the visible rows or columns without deleting the hidden rows or columns in Excel? This tutorial will introduce the ways to delete visible rows only.

Sum values without or exclude subtotals in Excel
Supposing you have a list of data mixed with several subtotal cells, when you need to sum the total, all the subtotals are included in the final summing. See screenshot below. For summing values without the subtotals in Excel, what would you do? In this article, we will show you a quick method to achieve it.

Filter multiple columns simultaneously in Excel
When you apply the Filter function, after filtering one column, the next columns will be only filtered based on the result of the previous filtered column. It means that only AND criteria can be applied to more than one column. In this case, how could you apply both the AND and OR criteria to filter multiple columns simultaneously in Excel worksheet? Thy the method in this article.

Filter or select cells by cell color in Excel
Normally you can fill cells with different colors for any purposes in Excel. If you have a worksheet with using different colors to indicate different type of contents and you want to filter or select those cells by the cell color, you may get it done with the methods in this tutorial:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations