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 🙂
LARGE function is used to get the Largest k-th value from the range.
LARGE Function has two required arguments i.e. array, and k
This tutorial explains how to use the IFERROR function in Excel to catch and handle errors. It shows you how to replace errors with a blank cell, a different value, or a custom message. You’ll also learn how to use IFERROR with functions like VLOOKUP and INDEX MATCH, and how it compares to other error-checking functions like IF ISERROR and IFNA
TEXT function is used to change the formatting or appearances of the text. There are various types of formatting available.
Table of Content Introduction Benefits of Using Outlook and Excel for Work Allocation Setting Up Your Outlook-Based Excel Tool Managing Work Allocation Conclusion Download Free Excel Template Introduction Efficient work allocation is crucial for organizations…
The tutorial teaches you the basics of regression analysis and shows a few ways to do linear regression in Excel.
Imagine this: you have a lot of data and need to predict next year’s sales for your company. You’ve found many factors that could affect the sales, maybe even hundreds. But how do you know which ones really matter? You can use regression analysis in Excel. It will help answer this and other questions, like: Which factors are important and which ones don’t matter? How connected are these factors? And how confident can you be in your predictions?”
This tutorial explains how the TRANSPOSE function works and shows you the right way to use it to switch data in Excel.
Everyone has different preferences, even for work habits. Some people like to arrange data in vertical columns, while others prefer horizontal rows. If you ever need to switch the direction of your data quickly, the TRANSPOSE function can help