Introduction
The Subtotal function will come in all designs of MS Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower. Unlike different Excel functions that are designed to do just one certain issue, Subtotal is surprisingly very - it is able to do different arithmetic and reasonable operations such as counting, calculating the average.
Finding the minimal or maximum value, and more for a group of values, record or database. In this informative article, you'll come to understand about the employs of subtotal function in excel, the syntax and their frequent errors. Please read…
Why use Subtotal function?
The answer to the above mentioned issue is simple. The uses of subtotal function in Excel are as follows:
1. It may produce results dynamically
2. It ignores hidden lines
3. You should use SUBTOTAL to find total sum of filtered values
4. You are able to instantly produce SUBTOTALs using Excel Data ToolsThe syntax for SUBTOTAL Function is:
=SUBTOTAL (function_num, range)
Where,
- function_num represents number that specifies which function to use in calculating subtotals within a list.
- And range represents a range of cells for the list.
(See the table under for possible values for function_num).
Tips:
- When function_num is between 1-11, SUBTOTAL includes values that are hidden
- When function_num is between 101-111, SUBTOTAL excludes values that are hidden
An Example Showing Subtotal function
Initially, on a new spreadsheet input the data or open an excel file to find the sum, average, etc. For an example I have opened a file for ABC STORE and have used multiple subtotal functions.
From the screenshot, I have used:
- The formula =SUBTOTAL(9,E3:E8) results into the Sum (operation code = 9) of all the elements present in the range E3:E8.
- And =SUBTOTAL(1, E3:E8) results into the Average (operation code = 1) of all the elements present in the range E3:E8
- In the same way =SUBTOTAL(2, E3:E8) results into the Count(operation code = 2) of specified the range.
Some common Subtotal function errors in MS Excel
If your Subtotal formula returns a mistake, it's apt to be as a result of among the subsequent reasons:
- #VALUE! - The function_num argument is apart from an integer between 1 - 11 or 101 - 111;
- #DIV/0! - occurs in case a specified overview function has to perform a division by zero (e.g. calculating a typical or normal change for a variety of cells that doesn't include a single numeric value).
- #NAME? - The title of the Subtotal function is misspelt - the easier mistake to fix
Conclusion
That’s all! Hope you’ve learnt the basics of subtotal in Excel. Just try yourself.