Excel Function – SUBTOTAL


Excel Function “Subtotal” is most commonly used formula in excel and it can make your work easy while performing simple mathematical calculations because of variety of features like:

– You can use this function for different purposes instead of multiple functions i.e. SUM, AVERAGE, COUNT, MAX, MIN, Standard Deviation etc.

– It can help you to ignore values in filtered range or hidden values

In short this function is “ONE FORMULA FOR MANY”. Let’s learn this formula in detail

SUBTOTAL Function in Excel

Below are the Subtotal Formula syntax commonly used in Excel:

=SUBTOTAL(Function_num, Ref1,..)

1.  =SUBTOTAL(9,Excel Range) >> Here 9 is used for SUM function
2.  =SUBTOTAL(1,Excel Range) >> Here 1 is used for Average Function
3.  =SUBTOTAL(4,Excel Range) >> Here 4 is used for MAX function

Parameters:

Function_num: It denotes the function number like 9 is used for SUM function. Though once you write Subtotal formula in excel, you will get the screen tip however you may refer the next section for detailed function numbers which you may use for various purposes

– Ref1,Ref2…: It is basically a excel data range where your value exists. You may write Excel Cells or Excel range i.e. A2,A3,A4 or A2:A4 respectively

Function Number For Subtotal Formula

There are two types of number you will find while writing SUBTOTAL formula

– Single Digit: 1,2,3,4 etc.

– Three Digit: 101,102,103,104 etc.

But if you see these number screen tips, you will realize that these are using the same function. Ever wondered why. Here is the reason:

Single digit function number includes all values listed in the range for selected calculation including hidden cells, however consider only visible values in filtered list

– Three digit function number includes only visible values listed in the range for selected calculation and avoids hidden cells irrespective of filtered list or hidden rows

So three digit function number is more useful than Single digit number. Here you go for detailed listing:

 
Function_Num - 1 digit Function_Num - 3 digit Function Used For
1
101
AVERAGE
2
102
COUNT
3
103
COUNTA
4
104
MAX
5
105
MIN
6
106
PRODUCT
7
107
STDEV.S
8
108
STDEV.P
9
109
SUM
10
110
VAR.S
11
111
VAR.P

So you should use Function_number as per your requirement and select the range. That’s all you need to do wtih this. 

Things To Remember

 – 1 digit function number includes all the numbers given in an Excel Range and works fine with filtered list

 – 3 digit function number includes all the numbers given in an Excel Range and does not calculate hidden, filtered list

 – It is suggested that SUBTOTAL should be used only in Vertical Ranges because if you use this function in a horizontal ranges, it will not exclude any hidden range values

How to use SUBTOTAL function in Excel

  • See below image: you may write in a cell "=Subtotal("Type Desired Calculation Number","Select Range")

    Filtered Range Results
SUBTOTAL Function - Filtered Range
  • Results Without Filter
SUBTOTAL Function in Excel

Hope you understood the concept now and can see the difference in above two images. Please comment below for any questions and yes do not forget to subscribe us.

Happy Reading 🙂

Are you enjoying learning with us, please follow us on Social Media 

Leave a Reply

avatar
How to use Excel Workday Formula in Excel
DATA ANALYSIS TRICKS
EXCEL FUNCTION – WORKDAY

Are you working today? or Do you have Work Off or holiday today?  I am asking this question because I am gonna tell you the most commonly used function in Excel “Workday”. “Workday” as name suggest that it brings the day when you will be working or you are expecting …

What is SUM Function in Excel
DATA ANALYSIS TRICKS
Excel Function – Sum Formula

Excel is a mathematical spreadsheet where you can perform multiple calculations with the help of Excel Formulas. These are automated formulas which refreshes automatically once you refresh your data in a given range. Here you should know about “Excel Ranges” before starting to use Excel Formulas. You can SUM (Add) …

Excel Dashboard and Reporting
DATA ANALYSIS TRICKS
Best Excel Resources For 2020

While starting Excelsirji.Com, it is always been critical for me to find the best to amaze the viewer experience. So I spent many hours on web to read, explore amazing excel content which I really found helpful to grow as a perfect data analyst. I started learning new things and …

error

Like the Article? Please spread the word :)

Facebook
Facebook
YouTube
YouTube
Pinterest
Pinterest
LinkedIn