Using the Excel SUBTOTAL Function

in this Excel video I'm going to show you how to use the Excel subtotal function and I'll compare and contrast it to the sum function so here I have an employee list and you can see there's different departments that the employees are part of and we have their hourly wages hours worked and income I would like to add up the hours worked and normally I might do that at the bottom of the data or to the side but just so that you can see it better in the spreadsheet I've put it up here at the top so how would we normally do this normally we would use the sum function so I could click here in cell E2 for example and many people are used to using the autosum function when you want to sum and that's fine in this case it'll be a little tricky because this cell is above the numbers that need to be summed so I'm just going to type in equals sum left parenthesis and then I'll select the range that I want to sum up and instead of just clicking and dragging I'm just going to type in the cell references so E4 colon which represents the word through so E4 through let's say e 400 I should put in my right parenthesis and then I'll tap enter on the keyboard and it's added up all of the Hours worked for all of the employees in this list now let's do the same thing but using the subtotal function I click on Cell E1 type equals subtotal left parenthesis and look I get a whole menu of different types of subtotals that I could generate one of them though is sum the code for sum is the number nine so I could just type the number nine here or I could double click on sum and it puts the number nine in for me next I'll put in a comma and next Excel is looking for a cell reference so it's the same as with my sum function so I'll just type in e 4 through e400 I should put in my right parenthesis Tau tap enter on the keyboard and I get the exact same result so you may be asking yourself why would you ever use subtotal when the word sum is shorter than subtotal why not just use sum let's take a look at why in addition to the fact that the subtotal function lets you choose from a list of different types of subtotals average sum Max Min Etc in addition to that the subtotal function allows you to calculate only the items that are currently visible that are not filtered out so as you can see I have my data in a table here and there are some filters ready to be applied if you haven't already watched my videos on filters and filtering in Excel you really need to do that but basically I can use these filters to focus in on exactly the data I'm looking for in this case I want to know the total amount of hours worked by the sales department so I just click this button here and I'm going to deselect all and then I'll just select select sales click OK and now I'm only seeing those employees that are in the sales department now look at my sum the sum is still exactly the same as it was before Excel is still counting the records that are not visible if you look over here we only see Row 8 and row 12 it seems to be skipping rows 9 through 11 and yet Excel is still calculating it here with the sum function what about subtotal no the subtotal function is only adding up the hours worked for those employees that are currently visible in other words the sales department so this is working great I'm going to click the filter button again and this time I'll deselect sales I want to know how many hours were worked by the inventory Department I click OK and the subtotal is updated here now I can switch and go to Administration let's take a look at that and you can see the total hours there so as you can see in some cases that subtotal function is what you want to use instead of the sum function or the average function or Max or Min there are times when you'll want to use the subtotal function and then specify the kind of subtotal you want thanks for watching this tutorial I hope you found it to be helpful if you did please like follow And subscribe and when you do click the bell and you'll be notified when I post another video if you'd like to support my channel consider clicking the thanks button below the video or you can support me through my patreon account or by buying Channel merch and you'll see information about those options in the description below the video speaking of patreon I have to give a quick shout out to my five dollar patreon supporters thank you so much for all you do to support the channel some of you have been with me for a year and a half two years or longer and some are brand new but regardless I appreciate you so much thank you for supporting the work that I'm doing

Channel: Technology for Teachers and Students

Views: 98,246

Keywords: subtotal function, exel subtotal, exel subtotal function, excel subtotals, excel subtotals funxion, excel functions, excel funxions, subtotal funxion

Id: f7ITbGlYt7A

Length: 4min 56sec (296 seconds)

Published: Mon Jul 03 2023