Excel Subtotal Showdown: 9 vs. 109 - Episode 2269

Microsoft Excel Tutorial: Difference between 9 & 109 in Excel Subtotal function Welcome to episode 2269 of the MrExcel netcast, where we dive into the world of Excel and uncover its hidden secrets. In this episode, we will be discussing the difference between the subtotal 9 and 109 formulas. I recently encountered this question during a power Excel seminar in Omaha and thought it would be a great topic to cover. So, what exactly is the difference between these two formulas? Well, the subtotal 9 formula is designed for when you are hiding rows by filtering. This means that if you filter your data to only show certain rows, the subtotal 9 formula will only include those visible rows in its calculation. However, if you manually hide rows without using the filter, the subtotal 9 formula will not take those hidden rows into account. This is where the subtotal 109 formula comes in. It is designed to respect any manually hidden rows, in addition to the filtered rows. So, if you are randomly hiding rows without using the filter, the subtotal 109 formula will still include those hidden rows in its calculation. This is explained in Excel help as the difference between 1 to 11 and 101 to 111, with 11 different functions available. The subtotal 9 formula falls under the category of "some" and is the one I use most often. However, I did encounter a strange bug while testing this formula. When I first used the subtotal 9 formula, it seemed to be ignoring the manually hidden rows, even though it should have included them. After further investigation, I discovered that this only happens if I have already applied a filter to the data. It seems that the subtotal 9 formula is then taking into account both the filtered and manually hidden rows, which is not its intended function. I will be passing this information on to the Excel team to look into. So, if you encounter this issue, try using the subtotal 109 formula instead. I hope this explanation has cleared up any confusion about the difference between subtotal 9 and 109. Thank you for tuning in to this episode of the MrExcel netcast. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time! Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-on-youtube/ #excel #microsoft #exceltutorial #exceltips #microsoftexcel #excelformula #excelformulasandfunctions #evergreen This video answers these common search terms: how to subtotal function in excel how to use subtotal function excel how to use subtotal function in excel how to use subtotal function in excel table how to use a subtotal function in excel what does the subtotal function do in excel how to use the subtotal function in excel how to use excel subtotal function formula to create a subtotal in excel how to use subtotal formula in excel how to make subtotal formula in excel how to write subtotal formula in excel how to create subtotal formula in excel how to enter a subtotal formula in excel what is the subtotal versus sum formula in excel Table of Contents: (00:00) Explanation of Subtotal 9 and 109 in Excel (00:21) Subtotal 9 for filtering, Subtotal 109 for manually hiding rows (00:36) Bug with Subtotal 9 when manually hiding rows (01:02) Explanation of 1 to 11 versus 101 to 111 functions (02:04) Clicking Like really helps the algorithm Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads/1154098/

• #education
• #microsoft_excel
• #excel
• #bill_jelen
• #mrexcel
• #excel_functions_and_formulas
• #subtotal_function_in_excel
• #subtotal_hidden_rows
• #subtotal_ignore_hidden_rows
• #subtotal_9_versus_109
• #subtotal_in_excel
• #how_to_use_subtotal_formula_in_excel
• #how_to_make_subtotal_formula_in_excel
• #how_to_create_subtotal_formula_in_excel
• #how_to_enter_a_subtotal_formula_in_excel
• #how_to_use_subtotal_function_in_excel
• #how_to_use_subtotal_function_excel