How To Use Subtotal Function In Excel

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 varied – it is able to do different arithmetic and reasonable operations such as counting and calculating the average.

It helps in finding the minimum or maximum value and more for a group of values, records, or databases. In this informative article, you’ll come to understand the employs of subtotal functions in excel, the syntax, and their frequent errors.

Why use the Subtotal function?

The answer to the issue mentioned above 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 the total Sum of filtered values
4. You are able to produce SUBTOTALs using Excel Data Tools instantly

The Syntax For SUBTOTAL Function Is:

=SUBTOTAL (function_num, range)

Where,

•       function_numrepresents a number that specifies which function to use in calculating subtotals within a list.
• And rangerepresent a range of cells for the list.

(See the table under for possible values for function_num).

Tips:

• When function_numis between 1-11, SUBTOTAL includes values that are hidden
• When function_numis between 101-111, SUBTOTAL excludes values that are hidden

In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.

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 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 in 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 in the Count (operation code = 2) specified the range.

Note: When you use a Subtotal formula with a summary function like SUM, AVERAGE, or COUNT, it calculates only cells with numbers ignoring blanks and cells containing non-numeric values.

 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_numargument is apart from an integer between 1 – 11 or 101 – 111;
• #DIV/0! In case a specified overview function has to perform a division by zero (e.g., calculating a typical change for a variety of cells that doesn’t include a single numeric value).
• #NAME? – The title of the Subtotal function is misspelled – the easier mistake to fix

Tip: If you don’t feel confident with the Excel SUBTOTAL function yet, you can use the built-in SUBTOTAL feature and have the formulas inserted for you automatically.

Conclusion

That’s all! I hope you’ve learned the basics of Subtotal in Excel. Just try yourself.