As mentioned, using 1-11 for the function_num argument will ensure that SUBTOTAL includes hidden values, while 101-111 ignores them.While SUBTOTAL has advantages, there are also a few things you’ll need to consider before you start using it. Things to consider before using SUBTOTAL in Excel This will create a combined subtotal using the total data across all the included cell ranges. If you want to take this further, you can add up to 254 different cell ranges, one by one. The ref1 argument is required, as this is the initial range of cells that you’ll want to subtotal using one of the 11 functions. Using the numbers 1-11 allows SUBTOTAL to include numbers from hidden rows or columns, while using 101-111 allows SUBTOTAL to exclude them. The variations in number determine whether SUBTOTAL includes or ignores hidden numbers. Instead, you’ll need to use a number to represent the function you wish to use. In this example formula, the function_num argument refers to the function you wish to use with SUBTOTAL.Īs we’ve mentioned, 11 different functions are available, but you can’t use = SUBTOTAL(SUM, and expect it to work. Further arguments,starting with ref2, are optional. SUBTOTAL uses up to 11 different mathematical functions (from AVERAGE to VAR.P) to find the right total value, while working within the parameters of the function (ignoring other SUBTOTAL formulas, for instance).Ī SUBTOTAL formula is easily created using the following structure:Ī formula using SUBTOTAL has at least two arguments: function_num and ref1. The SUBTOTAL function works by calculating the total value of a cell range, based on another mathematical function. How does the SUBTOTAL function work in Microsoft Excel? If you want a SUM formula that works with filtered cells and ignores other SUBTOTAL formulas, then using SUBTOTAL is best, although a pivot table could work in a similar way. 11 different subtotal methods are available, each matching an existing Excel function. Thankfully, you can use other functions as part of a SUBTOTAL formula.
SUBTOTAL is also useful for ignoring hidden values, which other functions (like SUM) can’t do. If you filter a table using a value, the SUBTOTAL formula updates, recognizing the filter and excluding cells accordingly. SUBTOTAL works with other Excel features, such as cell filtering. You can insert a formula using SUBTOTAL into a range of cells without it affecting the overall total, because SUBTOTAL ignores other cells that contain a SUBTOTAL formula. While it’s true that you don’t necessarily need to use SUBTOTAL, it makes targeted data analysis a lot easier. What is the SUBTOTAL function and what is it used for?Īt first glance, you might wonder: what is the point of the SUBTOTAL function? After all, it’s easy enough to find a subtotal from a range of cells using existing functions like SUMIF or AVERAGE.
#HOW TO USE THE QUICK ANALYSIS BUTTON TO SUM THE RANGE HOW TO#
0 kommentar(er)
