Calculate the total value of the filtered list in Excel
Network administration - Filter is a very useful and easy to use feature in Microsoft Excel. With filters, you can quickly limit data to only show the necessary information. However, how to calculate the total value of the filtered list? The following article will help you answer the above question.
- MS Excel 2007 - Lesson 8: Sort and Filter
The tutorial is implemented in Excel 2003, which is quite old, but you can still apply this calculation on newer Excel versions such as Excel 2007, Excel 2010, Excel 2013, Excel 2016, because of the user guide Excel Subtotal function.
The figure below shows a filtered list. You can look at the column on the left and notice that many rows are not displayed because the filter is not satisfied.
The next figure shows you the results when we try to calculate the total value with the SUM () function.
At first glance, it is easy to see incorrect results: the total value is too large, why? That's because the SUM () function calculates all values from D14 to D64, not just summing the filtered values . There is no way for the SUM () function to know that we want to exclude values that do not satisfy the filter.
The solution to this problem is very simple. Just click AutoSum , Excel will automatically call SUBTOTAL () instead of SUM (). This function will look at the entire list from D6 to D82 but only calculate the total value of the filter match.
About SUBTOTAL ()
The SUBTOTAL () function will look at the entire list of values in column D and only calculate the filter match values. You can look at the image above and guess that it is thanks to declaring argument 9. However, this argument tells Excel that we want to calculate TOTAL reference values. The following table lists accepted arguments:
Including hidden values
Ignore hidden values
Jaw
first
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 ()
ten
110
VAR ()
11
111
VARP ()
After looking at the table above, you are probably wondering the difference between 9 and 109. When we use argument 9, SUBTOTAL () will sum the hidden values. When we use argument 109, SUBTOTAL () will ignore hidden values. We need to distinguish the hidden value and the value that is removed due to not satisfying the filter . Hiding a row can be done by right-clicking on the row order then selecting Hide . This is completely different from the rows that are not displayed because the filter is not satisfied.
See more:
- How to use the Power function in Excel
- How to use the Round function in Excel
- How to use Vlookup function in Excel
You should read it
- SUM function in Excel: How to use SUM to calculate totals in Excel - SUM function in Excel
- 3 ways to calculate totals in Excel
- How to use DSUM function in Excel
- How to calculate the total value based on multiple conditions in Excel
- How to use the SUM function to calculate totals in Excel
- How to use the kernel function (PRODUCT function) in Excel
- How to automatically calculate and copy formulas in Excel
- Tips for quickly calculating Totals in Excel
May be interested
- DATEDIF () function (calculate the total number of years, total months or total days from two given periods) in Excelthe datedif () function helps you calculate the total number of years, the total number of months, or the total number of days from two given periods.
- The easiest way to calculate the percentage (%)calculating percentages on excel doesn't have to use excel functions, it's just a normal calculation. so how to calculate the fastest percentage, and also need the most accurate. the following article will guide you how to calculate the most accurate percentage value.
- Calculation of percentages in Excelcalculating percentages in excel is a basic calculation, with a very simple and quick calculation when you don't need to enter any functions.
- Instructions for calculating total lines and columns in Word 2013if you are working in word and need to calculate the value of an expression, you can do simple calculations such as summarizing, multiplying, and calculating the average value on the word without copying. data into excel to calculate and paste the results into word.
- How to use the SUM function to calculate totals in Excelsum is a popular and very useful excel function, and is also a basic arithmetic function. as its name suggests, the sum function is used to calculate totals in excel. and the parameters can be single parameters or ranges of cells. in this article, tipsmake.com will guide you to use the sum function to calculate the sum in excel, the common errors when calculating sum by sum and how to fix it.
- How to calculate percentage, format percentage in Excelwant to calculate percentages in excel? the following article from tipsmake will help you do it quickly. you just need to remember the formula and follow a few simple steps.
- How to Create a Mortgage Calculator With Microsoft Excelthis wikihow teaches you how to calculate your mortgage-related expenses like interest, monthly payments, and total loan amount using a microsoft excel spreadsheet. once you've done this, you can also create a payment schedule that uses...
- How to use the kernel function (PRODUCT function) in Excelin excel, product helps users to calculate product values, multiply arguments together, and return their results correctly.
- How to calculate totals in Excelhow to calculate totals in excel. when it comes to summarizing in excel, you often think of using the sum function to sum the data in excel. but there are many cases where a simple data area, just a column or a row of data, you can use fish
- How to calculate the number of days in Excelhow many days from january 9, 2015 to june 12, 2017? if you can find results within 5 seconds, you are truly a genius. however, this is no longer a problem when you know how to use excel.