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 Tools

The 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).

How To Use Subtotal Function In Excel 1

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
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 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.
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_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
Tip. If that 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! Hope you’ve learnt the basics of subtotal in Excel. Just try yourself.