Example of Subtotal Function in Excel: In the below example I have used multiple subtotal functions. The formula =SUBTOTAL(9,B2:B11) results into the Sum (operation code = 9) of all the elements present in the range B2:B11. And =SUBTOTAL(1,B2:B11) results into the Average (operat...
If you enjoyed learning about this function, you’d be amazed to know how vast the entire function library of Excel is. Some key functions of Excel include the VLOOKUP, SUMIF, and IF functions. Want to learn them?Enroll in my 30-minute free email course here to get started. It will ...
For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by theHide Rowscommand under theHide & Unhidesubmenu of theFormatcommand in theCellsgroup on theHometab in the Excel desktop application. Use these constants when you want to subtotal hidden...
argument allows you to insert one of 11 functions using its corresponding number. The first 11 include hidden rows, while the second 11 exclude them. So, simply insert the number forthe function you needand how you want to handle the hidden rows. As an example of the SUBTOTAL function, w...
TheSUBTOTALfunction returns a list or database and falls under the Math/Trig function category. It offers11Excel functions that can be executed by specifying the corresponding argument. Syntax SUBTOTAL(function_num,ref1,[ref2],…) Return ...
If weapply a filterto filter out the value of “Banana” in col A, the two functions remain the same. If we manually hide the rows, we see the difference. The 109 function was able to ignore the hidden row while the 9 function didn’t. ...
Excel can automatically add SUBTOTAL functions for you. 1. The first option. Create a table and add a total row to the bottom of the table. Next, click any cell in the last row to automatically add a SUBTOTAL function. Explanation: in this example, we also filtered by Country. Excel ...
In Microsoft Excel, the Subtotal feature is not limited to only totaling subsets of values within a data set. It allows you to group and summarize your data using SUM, COUNT, AVERAGE, MIN, MAX and other functions. Additionally, it creates a hierarchy of groups, known as an outline, which...
In this article, we will learn How to use the SUBTOTAL function in Excel.Obtain results on filtered rowsThe biggest problem with SUM, AVERAGE, COUNT and other NUMBER functions is, that they include rows that are hidden by filter. They don’t work correctly when we want to sum, count, ...
Sign in to your account Sign in Excel Formulas and functions More functions SUBTOTAL function SUBTOTAL function Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Office for ...