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
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
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:
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
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 🙂
Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
SUMIFS function is used to get the “total sum” of values for matching criteria across range. SUMIFS Function has required and optional arguments
TRIM function is used to remove the additional spaces (i.e. spaces before/after/between the words) except for single space between words.
COUNTIFS function is used to get the total count for number of times the various criteria across ranges are met.
LEN function is used for counting number of characters in available string. The output of the function returns the count in new cell.
An ultimate guide for basic user to understand Excel Vlookup function. VLOOKUP is a vertical lookup which helps the user to extract the values from other columns (leftmost) basis on matching column string.
ROWS function is used to get the total count of rows in an array or in cells range in an excel worksheet.