Return the dynamic operations to the summary value in Excel
Use several types of summary functions in table


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

Return Value

Summarize value of the list

Key Notes

  • 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.

SUBTOTAL function returns the dynamic value if filter is active in Excel
The SUBTOTAL function returns the dynamic value if the filter is active.

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.

Return summary value even if rows are hidden manually in Excel
Return summary value even if rows are hidden manually

Keep Reading Similar Functions:

Counts cells with only Numbers in Excel


The COUNT function count of numbers within the reference list of arguments ignoring text & blank cells, =COUNT(A1:A10) will return 5 (only numeric).

Read More »
Find the average of numbers in Excel


The AVERAGE function returns an average of numbers positioned in one or multiple ranges, =AVERAGE(5,15,30) add numbers and divide by 3, and returns 16.67.

Read More »
Return the largest value in Excel


The MAX function returns the highest value as per cell range, if A1:A5 contains a list of numbers (2,8,10,5,6), the formula can be =MAX(A1:A5), returns 10.

Read More »
Return the smallest value in Excel


The MIN function returns the smallest numerical with the lowest value as per given reference of cell range.

Read More »
To add multiple numbers in Excel


The SUM function returns a number after adding multiple numeric arguments together =SUM(5,10,15) the Excel add numbers together and returns 30.

Read More »
Counts cells that are not blank in Excel


The COUNTA function count of numbers and text within the reference list of arguments ignoring blank cells, =COUNTA(A1:A10) will return 7 (disregard blank).

Read More »