Bonus tips for summing a range containing sub-totals: 1. SUM the entire range and divide by 2. Using the example data at 1:13 in the video the formula would be =SUM(C16:C45)/2 (credit for this tip goes to many people who commented below and emailed me. I also remember using this in my accounting days, but that was a long time ago, so appreciate those who reminded me) 2. Select the range C16:C47 > ALT+= (credit Bob Umlas)
Genius! I thought I knew how to use some of the basic functions of Excel - I didn't and I learn something new every time I watch one of your videos. Thank you for sharing your knowledge.
Wow, five minutes watching this (and five trying it out) will save me literally HOURS of work summing totals within our monthly timesheet template - Thank you so much, you're an Excel Hero!
Brilliant! With Grand Total, I've often used the formula =Sum(everything above)/2. As your list could be huge as long as you have subtotal for each category it would work as the value is double and you need to half it at 1:23
I always learn something new from your videos! The last tip selecting a range of sheets rather than individually was superb! Tip: When selecting non contiguous cells highlight the first cell press Shift + F8 then select the other cells, no need to hold down the Ctrl key.
Brilliant! I've been a really heavy user (introduced spreadsheets to KPMG(HK) in 1983 - Multiplan), and yet you teach me something new and useful with every video. Your Dashboards course (like your PQ & PP) is just awesome and I'm looking forward to starting Power BI. Any chance of doing a Power Automate course, or can anyone recommend a good one that's more than 'an introduction'?
Hi Lindsay, it's true you always get to learn so many cool excel tips and tricks from this platform. They also have very good courses for Power BI, Power Pivot,......, I know of some platform where you can learn Power Automate but would not want to use another person's platform without their express permission to promote competing platforms who offer similar or competing courses unless Mynda is cool with it. If there is a way you can hit me up i would be willing to offer one or two some suggestions for Power Automate.
Excellent and thank you Mynda. Another trick is that if the data is filtered and then we want to select the data in the same way for each additional row or column, using the AtuoSum or alt + =, SUBTOTAL function with the first argument equal to 9 to add the filtered values .
I love your videos, I must say, and I learn a lot :-) My tip is this, in terms of non-contiguous cells: Select all your numbers, including the subtotals, and all the way down to your grand total. Then click AutoSum. Excel will just add up your subtotals and ignore constants. I would also prefer to enter the sum function by clicking the AutoSum tool a second time (not by pressing the Enter key), because that's where the mouse pointer already is placed. A bonus is that this works just like pressing Ctrl+Enter.
We can even use SUM with XLOOKUP for e.g. =SUM(XLOOKUP(I4,B4:E4,B5:E16)), where the XLOOKUP returns the entire record for the lookup_value column. SUM also works like conditional SUMIF(s) when used with new FILTER function for e.g. =SUM(FILTER(B5:E17,A5:A17="A")). Thanks various uses of SUM.
Mynda thank you those tips and tricks. And, I am looking forward to waiting for your Lambda Helper functions training course. Including of it, the Scan and Reduce functions are new being used by modern excel ones instead of ordinary Sum functions.
happy new year 2022 Mynda .great video .if I can suggest .we need as Arab counties. review for all excel formula with clear and easy explain as you do now .
I knew alt+= to get the sum for adjacent cells but didn't know I could apply for a range of blank cells and on non adjacent across the rows/columns. Thanks for sharing
The subtotal function with sum attribute works fine if there is an invalid cell or a NaN. The simple sum will fail. I also like the sumifs function which serves as the SQL group by function. Thank you so much for your vibrant tutorial
Amazing skills, fantastic delivery! :) THANK YOU for sharing your vast knowledge of one of my all time faves Excel! :D I've always loved the simplicity and power behind it. BTW, You are charting new horizons for those of us who don't dare but would love to know more! I subscribed immediately! You had me at " I simply Excel". :)
I can add one element. In your video at 1:16 Remove the blank rows including row 46. Select those same total cells. Do the alt+= Then select the grand total and do another alt+= It should do the sum formula but only include the subtotals.
She selected the first reference and pressed F4. You can also type $ before the row and column references to make them absolute without using the shortcut key. so a range of A1:A1 you'd end up with a formula that says $A$1:A1, when you autofill your following cells would then say, $A$1:A2, $A$1:A3 and so on.
The tip at 1.26. You could also add up all of the above by saying =sum(c1:c46)/2 Saves having to keep selecting cells and keeping Alt down. Great if you have loads of rows full of data.
One quick note you did not mention (I think). When summing "through" the workbook, each worksheet must be identical in the area where you are summing. If any worksheet is off by either a column or row, sum returns incorrect value.
Super Cool videos. can you create a video, where you make automatic comments on the results. Like: Profit is 2% higher than previous year. --------this should be all automatic :), would make powerpoint comments so much eaiser.
Although it wouldn’t strictly be the sum function itself, I’d mention the powerful AGGREGATE function as well. That function offers many more useful options.
Select a data range like A1 to B4, check the status bar & you will see SUM, COUNT & AVERAGE etc. Just left click on anyone & it will automatically be copied to Clipboard. Now you can paste it anywhere you want 😊
Hi Mynda. Two remarks: the extended SUM function is called a 3D function. The other is more important: As an MVP you should know (I mean spread this knowledge 😉): besides Autosum (this video is about SUM...) you can use the drop down arrow to calculate COUNT and AVERAGE. Nothing new under the sun....But didyou know that this formula is actually wrong? for SUM and COUNT is is not relevant, but for AVERAGE it is, if you have empty cells in your list then the calculation is incorrect. Because Autosum does not use the whole range, only the new total row. Check it out....greetings Bart
Bonus tips for summing a range containing sub-totals:
1. SUM the entire range and divide by 2. Using the example data at 1:13 in the video the formula would be =SUM(C16:C45)/2
(credit for this tip goes to many people who commented below and emailed me. I also remember using this in my accounting days, but that was a long time ago, so appreciate those who reminded me)
2. Select the range C16:C47 > ALT+=
(credit Bob Umlas)
Absolutely love these tricks, Saved so much time! Thanks a lot!
Great to hear!
More like this as a reminder of overlooked features.
Will do 😉
This SUMs up my life. Thank you!
😁 my pleasure!
Loved this - Role on work tomorrow!
Glad you liked it, Darren 😊
that running total trick is GENIUS!!! Thank you Mynda!!!
You're most welcome!
Wow! The CTRL key and CONCATENATE function just saved so much time! I was appending a comma to values. Live and Learn- thank you!!
Glad you discovered something new 😁
Thank you for sharing these tricks and tips.
My pleasure, Leo!
Genius! I thought I knew how to use some of the basic functions of Excel - I didn't and I learn something new every time I watch one of your videos. Thank you for sharing your knowledge.
Great to hear, Russell 😊
I’ve been looking for these tips my whole life, this video completes me. Thank you, thank you, 🙏
Aw, thanks so much, Chris!
Thank you so much for these tips. Really helpful !!
Great to hear, Filip!
Wow, five minutes watching this (and five trying it out) will save me literally HOURS of work summing totals within our monthly timesheet template - Thank you so much, you're an Excel Hero!
So pleased it’ll be useful to you, Anna 😊
This video really SUMmed up the topic nicely. Thanks.
😁 my pleasure!
Brilliant! With Grand Total, I've often used the formula =Sum(everything above)/2. As your list could be huge as long as you have subtotal for each category it would work as the value is double and you need to half it at 1:23
Thanks for sharing 👍 I use that sometimes too 😁
You won the bet!
:-) glad it was helpful, Tia.
your tricks are awesome. liked your 3d range selection techniques. thank you so much.
Awesome, thank you!
Very useful and easy to implement! Thank you eo much
Great to hear 😊
You are brilliant .. wish you all success as a true honest person.
Thank you.
So nice of you :-)
I always learn something new from your videos! The last tip selecting a range of sheets rather than individually was superb!
Tip: When selecting non contiguous cells highlight the first cell press Shift + F8 then select the other cells, no need to hold down the Ctrl key.
Nice tip! I’ll have to try it 👍
=SUM(C16:C45)/2 this also much easy and one step . thank you
Yes, thanks for sharing. This is in the pinned comment at the top that others have also shared :-)
Brilliant! I've been a really heavy user (introduced spreadsheets to KPMG(HK) in 1983 - Multiplan), and yet you teach me something new and useful with every video. Your Dashboards course (like your PQ & PP) is just awesome and I'm looking forward to starting Power BI. Any chance of doing a Power Automate course, or can anyone recommend a good one that's more than 'an introduction'?
So pleased to hear that, Lindsay! I don't have a Power Automate course and I'm not familiar with one I could recommend, sorry.
Hi Lindsay, it's true you always get to learn so many cool excel tips and tricks from this platform. They also have very good courses for Power BI, Power Pivot,......,
I know of some platform where you can learn Power Automate but would not want to use another person's platform without their express permission to promote competing platforms who offer similar or competing courses unless Mynda is cool with it. If there is a way you can hit me up i would be willing to offer one or two some suggestions for Power Automate.
Thanks on Running Total. That is a great shortcut
Glad you liked it 😊
Great tip, Mynda - thank you, and Happy New Year.
Peter
Thanks, Peter! And to you 😊
Very helpful, thank you!
You're welcome!
Very nice tips Mynda! Thank you for sharing. There are infinite possibilities with Excel in each feature and the more you explore, the better! 😊👍
Cheers, Vijay!
Wonderful for lazy people as I am ! Thank you.
Glad you liked it :-)
Excellent and thank you Mynda. Another trick is that if the data is filtered and then we want to select the data in the same way for each additional row or column, using the AtuoSum or alt + =, SUBTOTAL function with the first argument equal to 9 to add the filtered values .
Yes, good suggestion. SUBTOTAL or AGGREGATE is perfect for handling filter rows.
Great hints!
Thanks, Silvano!
Great job I was looking for it
Good to hear :-)
I love your videos, I must say, and I learn a lot :-) My tip is this, in terms of non-contiguous cells: Select all your numbers, including the subtotals, and all the way down to your grand total. Then click AutoSum. Excel will just add up your subtotals and ignore constants. I would also prefer to enter the sum function by clicking the AutoSum tool a second time (not by pressing the Enter key), because that's where the mouse pointer already is placed. A bonus is that this works just like pressing Ctrl+Enter.
Thanks for sharing, Thor!
Real useful tips.👍
Glad you think so!
Great tips Mynda! Thanks for sharing. Thumbs up!!
Cheers, Wayne!
Very nice explanation Pal
Thanks so much 😊
Great, Thank You!🤗
Cheers, John 😊
We can even use SUM with XLOOKUP for e.g. =SUM(XLOOKUP(I4,B4:E4,B5:E16)), where the XLOOKUP returns the entire record for the lookup_value column. SUM also works like conditional SUMIF(s) when used with new FILTER function for e.g. =SUM(FILTER(B5:E17,A5:A17="A")). Thanks various uses of SUM.
Yes, indeed, although I'm not sure I would when we have SUMIFS.
Great, i saved a lot of time with this shortcuts👍
Great to hear, Kevin!
Awesome... Thanks....❤
Welcome 😊
Valuable.. thanks..
My pleasure 😊
That was quick and interesting. Knew most, some I did not.
Glad it was worth your time 😊
Excellent. Thanks
Glad you liked it!
@@MyOnlineTrainingHub it was always something to learn from your videos. Is there any course on powe query
Yes, please see this page for my Power Query course: www.myonlinetraininghub.com/excel-power-query-course
EXCELlent SUMmary!
😁 Thanks!
Super.. thank you.
My pleasure :-)
Brilliant!
Cheers, David 😊
Mynda thank you those tips and tricks.
And,
I am looking forward to waiting for your Lambda Helper functions training course. Including of it, the Scan and Reduce functions are new being used by modern excel ones instead of ordinary Sum functions.
My pleasure, Emre! I haven’t played much with the new LAMBDA helper functions yet.
That’s gold… excellent stuff.
Glad you liked it 😊
Hi Mynda!Really Great Tips..Thank You :)
Thanks so much, Darryl 😊
You won the bet! :-)
Glad it was helpful, Max!
Very helpful, thank you very much for sharing 😀
My pleasure, Claire!
This was great, thanks Mynda and Happy New Year!
Cheers, Chris! And to you 😊
happy new year 2022 Mynda .great video .if I can suggest .we need as Arab counties. review for all excel formula with clear and easy explain as you do now .
Happy new year to you too! Glad you like my videos 🙏
Thank you for interesting and useful tips!
My pleasure, Andrew 😊
I knew alt+= to get the sum for adjacent cells but didn't know I could apply for a range of blank cells and on non adjacent across the rows/columns. Thanks for sharing
Glad you discovered something new :-)
Amazing video! Thank you so much!!
Thanks so much 😊
Nice video 📹 👍
Thank you! Cheers!
cool. thank you
My pleasure :-)
The subtotal function with sum attribute works fine if there is an invalid cell or a NaN. The simple sum will fail.
I also like the sumifs function which serves as the SQL group by function.
Thank you so much for your vibrant tutorial
Thanks for sharing, Mohammad!
Informative ! well simplified
Great to hear, John 😊
The times I have added a summary sheet and then gone through all the worksheets to add another cell 🤦♂ - Saved me again....
So pleased you found something you can use 😊
Sometimes it’s just the little things that can make the biggest difference with Excel lol
Thanks
Welcome :-)
Nothing new to me but, nice to have the collection in 1 place. 👌
Cheers, Roger 🙏
Amazing skills, fantastic delivery! :) THANK YOU for sharing your vast knowledge of one of my all time faves Excel! :D I've always loved the simplicity and power behind it. BTW, You are charting new horizons for those of us who don't dare but would love to know more! I subscribed immediately! You had me at " I simply Excel". :)
Wow, thanks for your kind words! Great to have you as a subscriber :-)
Also like the sum if function to work with check registers and update a sheet for budget performance
Thanks for sharing, Doug!
I can add one element. In your video at 1:16
Remove the blank rows including row 46. Select those same total cells. Do the alt+=
Then select the grand total and do another alt+=
It should do the sum formula but only include the subtotals.
Thanks for sharing, Jonathan!
Thank you. This is useful. Can you clarify how you repeated the first cell for the running total formula?
She selected the first reference and pressed F4. You can also type $ before the row and column references to make them absolute without using the shortcut key. so a range of A1:A1 you'd end up with a formula that says $A$1:A1, when you autofill your following cells would then say, $A$1:A2, $A$1:A3 and so on.
When you type in the colon Excel automatically enters the first cell reference again.
@@sweetheart72650 I understand how to lock a cell. Was actually asking how to repeat A1 without typing it
@@MyOnlineTrainingHub Thank you for the clarification
@@gyslaineatindehou1606 I'm so sorry I misunderstood.
Very helpful. Thanks
Please make video on VLOOKUP function
Here is a VLOOKUP video: ua-cam.com/video/4-5-TBhOP6Q/v-deo.html
@@MyOnlineTrainingHub Thanks a lot. Much appreciated.
The tip at 1.26. You could also add up all of the above by saying =sum(c1:c46)/2
Saves having to keep selecting cells and keeping Alt down. Great if you have loads of rows full of data.
Yes 🙏 I forgot all about that one, David. Thanks for sharing 😊
Always wondered if it is possible to use SUM in an array formula. I like to use arrays.
Glad you liked that tip, Benoit!
Nice
Thanks, Raj!
One quick note you did not mention (I think). When summing "through" the workbook, each worksheet must be identical in the area where you are summing. If any worksheet is off by either a column or row, sum returns incorrect value.
True!
Very nice 👌..can I take your advice ..
Glad it was helpful :-)
@@MyOnlineTrainingHub how can I grow up my channel 🙌
Super Cool videos. can you create a video, where you make automatic comments on the results. Like: Profit is 2% higher than previous year. --------this should be all automatic :), would make powerpoint comments so much eaiser.
You can use a dynamic label to create comments like this: www.myonlinetraininghub.com/excel-dynamic-text-labels
great. thank you
so this is excellent, but i have an issue, when i concat a number like 0.2, the 0 disappears, any ideas?
Although it wouldn’t strictly be the sum function itself, I’d mention the powerful AGGREGATE function as well. That function offers many more useful options.
Yes, Jose! AGGREGATE is on my video tutorial to do list. I have a written tutorial on it here: www.myonlinetraininghub.com/excel-aggregate-function
Ps _ there's also a 'cntrl_shift enter' version.
Do tell more.
see email. Thank you for asking.
Hi, Does Power query support appending of data from .xls files?
No, they need to be .xlsx files.
@@MyOnlineTrainingHub Thanks Mynda! Do you know any workaround for this except VBA?
Select a data range like A1 to B4, check the status bar & you will see SUM, COUNT & AVERAGE etc.
Just left click on anyone & it will automatically be copied to Clipboard. Now you can paste it anywhere you want 😊
Nice tips! Thanks for sharing 😊
Hey - this lecture do not meet your reputation - too entry level - Raghu
Thanks for the feedback. I do videos on all levels :-)
Hi I have values came from if formula i made, I could not get the total of those value since there are text in the cell
=IF(b9="Saturday","OFF","")&IF(b9"Saturday","8","") I wanted to get total of those 8s resulted from the formula
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Mynda. Two remarks: the extended SUM function is called a 3D function. The other is more important: As an MVP you should know (I mean spread this knowledge 😉): besides Autosum (this video is about SUM...) you can use the drop down arrow to calculate COUNT and AVERAGE. Nothing new under the sun....But didyou know that this formula is actually wrong? for SUM and COUNT is is not relevant, but for AVERAGE it is, if you have empty cells in your list then the calculation is incorrect. Because Autosum does not use the whole range, only the new total row. Check it out....greetings Bart
Great point about AVERAGE, Bart! Thanks for sharing.
OMG
Hope you liked it, John!