subtotal formula in excel

subtotal formula in excel Key Takeaways To use the SUBTOTAL function create a formula using the syntax SUBTOTAL function number reference 1 reference 2 You can also use the Subtotal feature by selecting your rows then clicking the Data tab In the Outline drop down menu select Subtotal

Formula Description Result SUBTOTAL 9 A2 A5 The sum of the subtotal of the cells A2 A5 using 9 as the first argument 303 SUBTOTAL 1 A2 A5 The average of the subtotal of the cells A2 A5 using 1 as the first argument 75 75 Notes The SUBTOTAL function always requires a numeric argument 1 through 11 101 through 111 as its first Use the SUBTOTAL function in Excel instead of SUM COUNT MAX etc to ignore rows hidden by a filter or to ignore manually hidden rows Rows Hidden by a Filter 1 For example the SUM function below calculates the total sales 2 Apply a filter

subtotal formula in excel

subtotal formula in excel
https://d3mm2s9r15iqcv.cloudfront.net/en/wp-content/uploads/old-blog-uploads/excel-subtotal-example.png

How To Use The SUBTOTAL Function In Excel Step by Step
https://d3mm2s9r15iqcv.cloudfront.net/en/wp-content/uploads/old-blog-uploads/excel-example-subtotal-range.png

How To Apply Subtotal Formula In Excel Sheet Printable Templates
https://cdn.educba.com/academy/wp-content/uploads/2019/05/SUBTOTAL-Formula-in-Excel-example-1-10.png

Syntax SUBTOTAL function num ref1 ref2 Return This function returns any numeric value Available in Microsoft Excel 365 Microsoft Excel 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 The SUBTOTAL function can perform a range of functions on a filtered part of a list or a database Here s the syntax of this function SUBTOTAL function num ref1 ref2 The Function num The function num tells SUBTOTAL which function is to be performed Here s the list of options available to you

IF A17 total SUBTOTAL 9 C2 C16 IF A17 average SUBTOTAL 1 C2 C16 IF A17 min SUBTOTAL 5 C2 C16 IF A17 max SUBTOTAL 4 C2 C16 And now depending on what function your user selects from the drop down list the corresponding Subtotal function will calculate values in filtered rows The SUBTOTAL function is a very handy function that allows us to perform different calculations on a filtered range The most common use is probably to find the SUM of a column that has filters applied to it The SUBTOTAL function will display the result of the visible cells only

More picture related to subtotal formula in excel

Find Average Values With Excel s SUBTOTAL Function
https://www.lifewire.com/thmb/41LSdG3ULacxfoTKYJpRvYcQfkU=/2096x1397/filters:no_upscale():max_bytes(150000):strip_icc()/SUBTOTALFunction-5be078bbc9e77c002692ea82.jpg

Excel SUBTOTAL Function Exceljet
https://exceljet.net/sites/default/files/styles/original_with_watermark/public/images/functions/main/excel subtotal function.png

SubTotal Function In Excel How To Use
http://i1.wp.com/www.exceltrick.com/wp-content/uploads/2012/12/Subtotal-Function-in-Excel-Example.png

The SUBTOTAL Function Calculates a summary statistic for a series of data Available statistics include but are not limited to average standard deviation count min and max The SUBTOTAL is one of the unique functions within spreadsheets because it can tell the difference between hidden cells and non hidden cells Subtotal Function in Excel is a wonderful formula that can be used to perform some specific arithmetic and logical operation on a defined range of cells Microsoft Excel defines Subtotal Function as It returns a subtotal in a list or database Subtotal formula takes two arguments 1 Operation Code 2 Range of Cells

[desc-10] [desc-11]

How To Use The SUBTOTAL Function In Excel Step by Step
https://d33wubrfki0l68.cloudfront.net/27121ce80be2a77ab5b855b22d674f55a6814e16/e5f5b/en/blog/uploads/excel-subtotal-function-num.png

Calculate Subtotals In Excel YouTube
http://i.ytimg.com/vi/WLXiFboAmME/maxresdefault.jpg

subtotal formula in excel - IF A17 total SUBTOTAL 9 C2 C16 IF A17 average SUBTOTAL 1 C2 C16 IF A17 min SUBTOTAL 5 C2 C16 IF A17 max SUBTOTAL 4 C2 C16 And now depending on what function your user selects from the drop down list the corresponding Subtotal function will calculate values in filtered rows