The sum function when filtering data is very important for Microsoft Excel users . This article will summarize for you how to filter and calculate sum in Excel using Subtotal.
While knowing how to sum a single spreadsheet column is useful, you may want to know the sum of different groups. The subtotal function in Excel is a handy way to calculate smaller amounts, separate from the final total. It's relatively easy to use in a spreadsheet, and it allows you to organize your data.
The subtotal function in Excel is a formula that calculates a range of values based on a specified operation. It appears as =SUBTOTAL(function_num, ref1, [ref2],...), where function_num refers to the operation you want the formula to perform. ref1, [ref2],... are the values you want Excel to include in the calculation. One of the most common uses for the subtotal function is to sum a column, but it can also perform the following operations:
The tutorial is done on Excel 2019, however you can still apply this calculation on other Excel versions such as Excel 2007, Excel 2010, Excel 2013, Excel 2016, because the tutorial uses the Excel Subtotal function.
Calculate the total value of a filtered list using the Subtotal function in Excel
Suppose we have a data table like the following, with filters already created.
| STT | Product Type | Product Name | Quantity | Unit price | Total amount |
| 1 | Book | Math exercises grade 3 | 2 | 10000 | 20000 |
| 2 | Pencil case | TL fabric pencil box | 2 | 30000 | 60000 |
| 3 | Pencil | TL paragraph pencil | 5 | 15000 | 75000 |
| 4 | Book | Math Grade 3 | 3 | 18000 | 54000 |
| 5 | Book | English Grade 3 | 3 | 19000 | 57000 |
After filtering out the list of products belonging to the book category, we have the following spreadsheet:
The requirement is to calculate the total price of books. If you use the SUM() function on the filtered data table above, the result you get will not be the total price of books but the total price of all products.
In this case, we use the SUBTOTAL function as follows, in cell E13, you enter:
=SUBTOTAL(9,H7:H11)In which, 9 is the argument value corresponding to the function to be used, here we want to calculate the sum, so the function to use is SUM, you can see in the table below, H7:H11 is the range to calculate the sum.
The result returned the total cost of books is 131,000.
About the SUBTOTAL() function
The SUBTOTAL() function will look at the entire list of values in column D and calculate only those values that satisfy the filter. You can look at the image above and guess that this is because we declared argument 9. However, this argument tells Excel that we want to calculate the SUM of the referenced values. The following table lists the accepted arguments:
| Including hidden values | Ignore hidden values | Jaw |
| 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() |
After looking at the table above, you may be wondering what the difference is between 9 and 109. When we use the argument 9, the SUBTOTAL() function will calculate the sum of all hidden values. When we use the argument 109, the SUBTOTAL() function will ignore hidden values. We need to clearly distinguish between hidden values and values that are excluded because they do not satisfy the filter . Hiding a row can be done by right-clicking on the row order and then selecting Hide . This is completely different from rows that are not displayed because they do not satisfy the filter.
The SUBTOTAL() function is also used to perform many other useful tasks. You can refer to the tutorial on the SUBTOTAL function of Quantrimang.com.
See also:
As you age, many of your bodys organs can weaken, but it doesnt have to be that way if you take care of yourself. Here are the most essential vitamins for women over 50.
Struggling to locate your Microsoft Teams ID or account details? This step-by-step guide shows exactly where to find your Microsoft Teams ID and account info on desktop, web, mobile, and more for seamless collaboration.
Unlock the power of Microsoft Teams transcription for meetings with this step-by-step guide. Learn how to enable transcription, prerequisites, and tips for accurate transcripts to boost productivity and accessibility. Perfect for teams in 2026.
Tired of Microsoft Teams "Unknown Error" crashing your meetings? Discover step-by-step troubleshooting fixes that work instantly. Clear cache, reset app, and more—no tech skills needed!
Not all supplements are beneficial for irritable bowel syndrome (IBS), some may even worsen your symptoms.
Struggling with Microsoft Teams Error AADSTS70008: Expired Security Token? Discover step-by-step fixes, from quick sign-outs to advanced cache clears, to get back to seamless collaboration instantly. Updated with the latest solutions.
Tired of Microsoft Teams Error C Data crashing your meetings? Get proven, step-by-step solutions to clear cache, reset, and restore seamless teamwork. Fast, reliable fixes for the latest Teams versions.
Discover how to use Microsoft Teams background images effectively with step-by-step guides, custom upload tips, and best practices to make your video calls professional and engaging. Elevate your remote work game today!
Discover how to use Microsoft Teams Web App on any browser effortlessly. Step-by-step guide, tips, and troubleshooting for smooth collaboration anywhere. No downloads needed!
Tired of the frustrating Microsoft Teams "User Not Authorized" error blocking your meetings? Discover proven, step-by-step solutions to fix it fast and reclaim your productivity. Works on desktop, web, and mobile.
Struggling with Microsoft Teams errors on iPhone or iOS? Discover step-by-step fixes for crashes, sign-in issues, and more. Get back to seamless teamwork in minutes—no tech expertise needed!
Battling Microsoft Teams Tasks sync error? Get instant relief with our proven, step-by-step fixes for Tasks not syncing in Teams. Boost productivity now—no tech expertise needed!
Unlock the power of Microsoft Teams Breakout Rooms with this 2026 beginner tutorial. Step-by-step guide to create, manage, and elevate your virtual meetings effortlessly. Perfect for educators, teams, and beginners!
Struggling with Microsoft Teams "Error N" network errors? Get step-by-step fixes for Solving Microsoft Teams "Error N" Network problems, from quick restarts to advanced troubleshooting. Restore seamless calling and meetings now!
Tired of Microsoft Teams "Media Error" ruining your video shares? Follow our step-by-step guide with the latest fixes to get videos playing smoothly again – no restarts needed!
