Returns multiple types of calculation in a data range
The Excel SUBTOTAL function returns value using any of 11 calculations depending upon the user input. It is a flexible function that provides the ability to summarize a list or data in multiple ways by determining the actual function.
=SUBTOTAL (function_num, ref1, [ref2], …)
function_num – To determine which type of calculation
ref1 – The first range of cells or array to calculate
ref2 [optional] – The second range of cells or array to calculate
Summarize value of the list
- The SUBTOTAL function is a versatile function with a misleading name as it does many different Excel operations apart from SUM function. Like AVERAGE, MAX, MIN, COUNTA, and other functions mentioned below.
- The SUBTOTAL function ignores any cells of reference data that already contain a formula with SUBTOTAL in them.
- Up to 255 ranges of numbers are allowed.
Following are different Excel functions that you can use by using SUBTOTAL functions alone:
(includes hidden values)
(ignores hidden values)
The most interesting fact of the SUBTOTAL function is that it calculates data ignoring hidden row.
Calculate summary data even if rows are hidden
Let’s take an example where you count the number of sales figure in our data sets that returns 12, using the Excel COUNT function.
This function will always return 12 as the number of data irrespective the cell is hidden or visible after the user applies a filter in the table. But if we use the SUBTOTAL function the count will vary depending on how many rows are visible.
In case the first argument is more than 100, the SUBTOTAL function behaves a bit differently.
Unlike previously where we required a filter table to ignores the hidden row if we opt for arguments more than 100 then it does not include data that were hidden manually but exclude data in rows that were hidden as a result of filtering.