I love this and your others videos. I really appreciate what you doing, the way your explain and the pertinent of your topics. I'm improving my knowledge in Excel thanks to you, nobody show the level of knowledge that you have and the way you teach. Sometimes I'm lost, but it's for my level of English. Thank you very much.
Thank you for your comment. We also love his channel along with anothher simple TH-cam channel: th-cam.com/users/UniversalPahji for excel simple tricks!!
You save me with this video, I need to tell you this, I was trying to obtain subtotal; my first language is Spanish so I sought for instructions in Spanish, but I'm using an excel that's all in English; this laptop was given to me by an American company well, anyway, In Spanish instead of a coma* is this sign ; so I was getting frustrated 'cause I was not getting the formula, if it wasn't for you I wouldn't , thanks a lot!
Hi Jon.. thanks for this second half of the SUBTOTAL lesson. It is a strange inconsistency that the Subtotal feature (from the ribbon) excludes the last Subtotal for the range defined in the Grand Total. Worth mentioning that the Subtotal feature (from the ribbon) can also remove the subtotals and row groupings for you via the Remove All button on the Subtotal dialog box. Another good trick.. if you want function_num 109 instead of 9, you can just do a Find and Replace.. i.e. Find "SUBTOTAL(9" and Replace with "SUBTOTAL(109" against the desired range.. after you have set up the data using the Subtotal feature (from the ribbon). This saves the time of manually modifying each formula.. handy if you have many. Thanks for the lesson.. always learn something new and interesting at your channel. Thumbs up!! PS - Good tip below by Eliot that the data must be sorted by primary grouping before using Subtotal.. too bad Excel will not automatically recognize like items in the primary group and do that automagically for the user :))
Hi Jon, I use Subtotal in cost reports (excel structured table) with slicers, pivot tables are way beyond most the people I work with. However, I include the header row in the sum range to capture any inserted rows at the top, I also include a color filled thin row above the subtotal - included in the sum range - to try & capture any inserted rows at the bottom, although this isn't foolproof if the user inserts a row immediately above the total row, this isn't included in the sum range. Summing the whole range with subtotal is safer than selecting individual cells in a sum formula. Another safe guard is to use a subtotal formula outside of the table - =SUBTOTAL(109,CostSummary[Cost]) - using the structured table reference, you can then be confident that everything within the table is included in the sum range.
You didn't mention, but I think important for the Subtotal Feature ... the item for subtotaling (in your example "Product") must be sorted and grouped before subtotaling. If an item is out of order (e.g., a single Product B is below Product C), then the Subtotal feature will add a second Subtotal for the misplaced item.
Very helpful video, thank you! Question please, is there a way to select ALL the boxes for a 'sum' from the Subtotal dialog? I have hundreds of columns I need to sum by different categories. Thank you!
Hi Jon. Excellent tutorial! I am curious where you explain how to get the number columns on the left of your table. (1,2,3) in your video. If there is another video that includes this, I haven't located it yet. Thanks.
I tried subtotal in a dataset with country and total income but it gave the same country where then once. Probably because there are spaces and therefore sees same countries as diff categories?
Is there a way to chart multiple subtotal amounts in a pie chart showing as a percentage of the grand total, ie Product A is 30 % of the grand total, Product B is 15% of the grand total, etc but showing this in a pie chart??
Hi Could you please help me to resolve the following: Columns : Products, Price, Qty sold I want to sum(price *qty sold) as well as subtotal in case the filter is applied Eg. =Subtotal(9,Sum(B1:B10*C1:C10)) Thanks
I have been using the subtotal feature for a while but I have a question. Is there a way to show the information from the Price row in the subtotal row as it is currently blank. I have been dragging the cell information but I wonder if there is an easier way. Thanks for your videos!
Thank you, Mary! Dragging is a good option. Another option is to copy either one of the $ formulas (Revenue or Shipping) from the same row and paste it into each Price spot. A third option is to check the box next to Price in the Subtotal pop-up window. The Price was left blank on purpose since a sum of unit prices may not make sense (although an average of unit prices might). Hope that helps!
Hi Jon, Brilliant video, I have two questions and if you can help me will be appreciated. 1. How did you create the drop downs on the left of your screen. 2. If there is a number that is not part of the subtotal then how do we stop it from being added into the subtotal range at the end. In between my subtotals i have number and it adds that number to the main subtotal at the end. Regards,
Sir, please can you help me? I am beginner. Can you tell me that is it necessary to sign in to use excel, PowerPoint, word etc.? And also is ms office free to use or we have to pay for it?
pretty cool video, i like that its current. Thank you for this. you mention using pivot tables over this, does it mean that subfunction total is obsolete?
At 5:39 you discuss the subtotal feature in the Outline tab. I see Subtotal in the ribbon but it's greyed out. Why is that? What can I do to "fix" it so I can use "Subtotal". Group and Ungroup are both available.
Hi Jon, thanks for your videos. I have a simple question, not at all related to this video. I have a column of IDs (146 rows) and I want to randomly assign these to another column (about 50,000 rows). Please how do I go about it. Thanks in advance.
What if I want to add more data to my subtotal group. How do I refresh the subtotal groups to include the data that I just added with my currently formatted subtotal group?
Great vid! If you have a table of data and one of the columns has times in it. Is there a way to create a Subtotal for the column of time (ex. idle time) and have it sum that data?
Is there any way to subtotal and get a sales total but then manipulate other columns data that are strings? So it's a list of a customers orders, and I get the subtotal that's fine, but say another column has strings that could match or be diff..is there a way to make the subtotals show like specific data? (Like if the rows subtotaling included the words Personal it could say "Personal" in the subtotal?
Hi Jon, Thanks for your videos, all superb and useful in day to day works! I have one query, I have received the Cheque from one of my Customer but do not know from whom. How to validate with my available data. Maybe the Chq. amount for multiple invoices per customer record. Do we have any functions or VBA code. It’s like using sumifs and goalseek function. Pls. do the needful. Thanks in advance!
The biggest problem with th Subtotal feature it does not work with Excel Tables and the SUBTOTAL function only works as a a Total Row. That fact needs to be explicitly in any video on the Suntotal.
![[UNCUT] “บอสณวัฒน์” ลากไส้!! ใครหนุนหลัง "แม่ตั๊ก ป๋าเบียร์" I คนดังนั่งเคลียร์ I 30 ก.ย. 67](http://i.ytimg.com/vi/QlD7mU2KKRk/mqdefault.jpg)
Jon - you are an excellent educator!!! You break down topics and explain them very clearly. Thank you!
Jon your videos are proving to be immensely helpful in office. Thank you
This was very helpful for someone who is not too savvy with Excel functions- Thanks!
I love this and your others videos. I really appreciate what you doing, the way your explain and the pertinent of your topics. I'm improving my knowledge in Excel thanks to you, nobody show the level of knowledge that you have and the way you teach. Sometimes I'm lost, but it's for my level of English. Thank you very much.
Thank you for the nice feedback, wramos! I really appreciate and I'm happy to hear you are learning so much about Excel. Awesome! 🙌
Thank you for your comment. We also love his channel along with anothher simple TH-cam channel: th-cam.com/users/UniversalPahji for excel simple tricks!!
You save me with this video, I need to tell you this, I was trying to obtain subtotal; my first language is Spanish so I sought for instructions in Spanish, but I'm using an excel that's all in English; this laptop was given to me by an American company well, anyway, In Spanish instead of a coma* is this sign ; so I was getting frustrated 'cause I was not getting the formula, if it wasn't for you I wouldn't , thanks a lot!
The best explanation by far!! Thank you! I look like a genius now!
Hi Jon.. thanks for this second half of the SUBTOTAL lesson. It is a strange inconsistency that the Subtotal feature (from the ribbon) excludes the last Subtotal for the range defined in the Grand Total. Worth mentioning that the Subtotal feature (from the ribbon) can also remove the subtotals and row groupings for you via the Remove All button on the Subtotal dialog box. Another good trick.. if you want function_num 109 instead of 9, you can just do a Find and Replace.. i.e. Find "SUBTOTAL(9" and Replace with "SUBTOTAL(109" against the desired range.. after you have set up the data using the Subtotal feature (from the ribbon). This saves the time of manually modifying each formula.. handy if you have many. Thanks for the lesson.. always learn something new and interesting at your channel. Thumbs up!!
PS - Good tip below by Eliot that the data must be sorted by primary grouping before using Subtotal.. too bad Excel will not automatically recognize like items in the primary group and do that automagically for the user :))
I just realized how much I had taken for granted the subtotal function haha.
Such a valuable knowledge in just 8 minutes! 😊
Great video. Who the hell down votes a good video?
This is very detailed and helpful. I can totally shorten my time doing my work. More power!
Thanks for your feedback, Lil! :)
Thank you for providing explanations for both Subtotal systems. The feature is so much more fun to play with. I can't wait to tell my nerd club!
Thank you Jon, I appreciate your comprehensive explanations.
Keep it up!!
Thank you very much. Very well explained and comprehensible.
Thank you!!! You are so good at explaining this in a way even I can easily follow.
Thanks for your feedback, @user-xo7zi5li1r 😀
Jon, your videos are awesome. Thank you so much!
This video helped me learn more about excel, for own knowledge and for my class. You have nice teeth.
OH MY GOSH! Thank you so much!! I've been having a problem with the hidden rows being part of the sum.
Jon, Excellent and thorough explanation with great examples.
Glad you liked it! 😀
Excellent explanation! Thank you!
I have not used Excel in several years(10) and it was driving me crazy as to where the Subtotal tool resided. Thanks so much for your video! #Rusty
I really admire your clear explanation in each & every video you upload! Thank you for spreading your knowledge across, you are doing a kind job😊
Thank you for your clear explanation 👍🤝
this was super helpful, you saved my life. thanks so much!!
Very good. Not sure why many videos on Subtotal overlook mentioning that the data must be sorted before applying Subtotal.
Thank you! 😀
Thanks Jon, this is really helpful - thanks for taking the time to share with us.
Glad it was helpful! 😊
It is absolute useful. I can't do my work without this video and (th-cam.com/video/-TRkUZYpsgU/w-d-xo.html)
Hi Jon,
I use Subtotal in cost reports (excel structured table) with slicers, pivot tables are way beyond most the people I work with. However, I include the header row in the sum range to capture any inserted rows at the top, I also include a color filled thin row above the subtotal - included in the sum range - to try & capture any inserted rows at the bottom, although this isn't foolproof if the user inserts a row immediately above the total row, this isn't included in the sum range.
Summing the whole range with subtotal is safer than selecting individual cells in a sum formula.
Another safe guard is to use a subtotal formula outside of the table - =SUBTOTAL(109,CostSummary[Cost]) - using the structured table reference, you can then be confident that everything within the table is included in the sum range.
You didn't mention, but I think important for the Subtotal Feature ... the item for subtotaling (in your example "Product") must be sorted and grouped before subtotaling. If an item is out of order (e.g., a single Product B is below Product C), then the Subtotal feature will add a second Subtotal for the misplaced item.
Great point Eliot! Thank you for mentioning that! 👍
I added your comment and a note on this to the blog post as well.
And also another reason to use pivot tables, as the source data for a pivot table does not need to be sorted.
Great video thanks for the all great info you are providing very helpful
thank you so much for this very helpful explanations......easy to understand :)
Uhm BLESS YOU, I literally have been panicking because I need to know this for a new job.
This is terrific stuff. Thanks!
Great Explanation...Thank You Jon :)
Fantastic lecture on subtotal.
Thanks
You're welcome, Abu! 😀
Excellent video.
Really Great!
Thanks, @fbergmansr! 😀
Thanks Jon, this is really helpful - thanks for taking the time to share with us.
Thank you, you have saved me an afternoon.🙂
You're welcome! 😀
brilliant tip. Thank you
Very nice 👌 thank you brother
Very helpful video, thank you! Question please, is there a way to select ALL the boxes for a 'sum' from the Subtotal dialog? I have hundreds of columns I need to sum by different categories. Thank you!
Thank you , helped alot.
Thank You so much.
Fantastic
Thank you so much!!!
Awesome, thanks!
I loved it!! great tip and great explanation as usual! Thank you Jon
Thanks Aline! I appreciate your support! 😊
Awesome. Great content.
Much appreciated! 😀
Thank you!
Hi Jon.
Excellent tutorial! I am curious where you explain how to get the number columns on the left of your table. (1,2,3) in your video. If there is another video that includes this, I haven't located it yet. Thanks.
Disregard, I re-watched and saw it adds it when do the outline.
thank you!!
Thank youuu!!
Thank you
Thanks!
pls show subtotal from negative positive numbers at a time. I badly need it.
I’m trying to have excel see a list of numbers and number them in order 1-27 which function would be best?
Thank you
Can I use the subtotal feature inside a Table?
is there a Subtotal button or create one?
sir why is it that the sub total product C total is not included in the grand total calculation , just want to know the reason.Thanks
thank you.
I tried subtotal in a dataset with country and total income but it gave the same country where then once. Probably because there are spaces and therefore sees same countries as diff categories?
Is there a way to chart multiple subtotal amounts in a pie chart showing as a percentage of the grand total, ie Product A is 30 % of the grand total, Product B is 15% of the grand total, etc but showing this in a pie chart??
thanks jon !
Thanks Pwint! 🙂
What if I want to keep the price in the subtotal row? How to do that?
If someone wants to apply subtotal on two columns one after another then how to do that
excellent
Thanks :)
Hi Could you please help me to resolve the following:
Columns : Products, Price, Qty sold
I want to sum(price *qty sold) as well as subtotal in case the filter is applied
Eg.
=Subtotal(9,Sum(B1:B10*C1:C10))
Thanks
I have been using the subtotal feature for a while but I have a question. Is there a way to show the information from the Price row in the subtotal row as it is currently blank. I have been dragging the cell information but I wonder if there is an easier way. Thanks for your videos!
Thank you, Mary! Dragging is a good option. Another option is to copy either one of the $ formulas (Revenue or Shipping) from the same row and paste it into each Price spot. A third option is to check the box next to Price in the Subtotal pop-up window. The Price was left blank on purpose since a sum of unit prices may not make sense (although an average of unit prices might). Hope that helps!
Hi Jon, does the subtotal function work if you have a row separating eg each product type .
Hello, thanks for this feature
Do you know how to do the same in Sheet please ? which add new rows like in Excel according to column
Thanks by advance
Hi Jon,
Brilliant video, I have two questions and if you can help me will be appreciated.
1. How did you create the drop downs on the left of your screen.
2. If there is a number that is not part of the subtotal then how do we stop it from being added into the subtotal range at the end. In between my subtotals i have number and it adds that number to the main subtotal at the end.
Regards,
hi Thank's
Sir, please can you help me? I am beginner. Can you tell me that is it necessary to sign in to use excel, PowerPoint, word etc.? And also is ms office free to use or we have to pay for it?
pretty cool video, i like that its current. Thank you for this. you mention using pivot tables over this, does it mean that subfunction total is obsolete?
Hi Jon, what if the Products were not sorted i.e. all Product A listed together, would the subtotal function still work?
At 5:39 you discuss the subtotal feature in the Outline tab. I see Subtotal in the ribbon but it's greyed out. Why is that? What can I do to "fix" it so I can use "Subtotal". Group and Ungroup are both available.
thanks for the video, can you use the subtotal with a condition for exambple sumif?
Looking to use TRIMMEAN calculation in SUBTOTAL function. Did I just discover the first thing that excel cannot do?
Hi...please filtering on a duplicated row takes much time..can u please help out🙏🙏🙏
Hi Jon, thanks for your videos. I have a simple question, not at all related to this video. I have a column of IDs (146 rows) and I want to randomly assign these to another column (about 50,000 rows). Please how do I go about it. Thanks in advance.
What if I want to add more data to my subtotal group. How do I refresh the subtotal groups to include the data that I just added with my currently formatted subtotal group?
Thanks , how to apply validation for subtotal, drop down list ?
Great vid! If you have a table of data and one of the columns has times in it. Is there a way to create a Subtotal for the column of time (ex. idle time) and have it sum that data?
Is there any way to subtotal and get a sales total but then manipulate other columns data that are strings? So it's a list of a customers orders, and I get the subtotal that's fine, but say another column has strings that could match or be diff..is there a way to make the subtotals show like specific data? (Like if the rows subtotaling included the words Personal it could say "Personal" in the subtotal?
Hi Jon, Thanks for your videos, all superb and useful in day to day works!
I have one query, I have received the Cheque from one of my Customer but do not know from whom. How to validate with my available data. Maybe the Chq. amount for multiple invoices per customer record. Do we have any functions or VBA code. It’s like using sumifs and goalseek function.
Pls. do the needful. Thanks in advance!
is there anyway to subtotal rounded numbers? You can sum(round...) but I can't get it for subtotal
Hello Jon , how can I exclude the outliers in the data set ??? Thank you :)
The biggest problem with th Subtotal feature it does not work with Excel Tables and the SUBTOTAL function only works as a a Total Row. That fact needs to be explicitly in any video on the Suntotal.
woooow what the heck? i been using excel for years and i just noticed the subtotal feature....
Hi but I don't understand why use 9?