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)

Wow! The CTRL key and CONCATENATE function just saved so much time! I was appending a comma to values. Live and Learn- thank you!!

More like this as a reminder of overlooked features.

This SUMs up my life. Thank you!

This was great, thanks Mynda and Happy New Year!

that running total trick is GENIUS!!! Thank you Mynda!!!

Great tip, Mynda - thank you, and Happy New Year. Peter

Great tips Mynda! Thanks for sharing. Thumbs up!!

This video really SUMmed up the topic nicely. Thanks.

your tricks are awesome. liked your 3d range selection techniques. thank you so much.

I’ve been looking for these tips my whole life, this video completes me. Thank you, thank you, 🙏

Very useful and easy to implement! Thank you eo much

Very nice tips Mynda! Thank you for sharing. There are infinite possibilities with Excel in each feature and the more you explore, the better! 😊👍

Thank you so much for these tips. Really helpful !!

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

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.

Loved this - Role on work tomorrow!

Thanks on Running Total. That is a great shortcut

You are brilliant .. wish you all success as a true honest person. Thank you.

Thank you for sharing these tricks and tips.

Thank you for interesting and useful tips!

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!

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.

That’s gold… excellent stuff.

Amazing video! Thank you so much!!

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.

Great job I was looking for it

Very helpful, thank you very much for sharing 😀

Great, i saved a lot of time with this shortcuts👍

Informative ! well simplified

Very helpful, thank you!

Hi Mynda!Really Great Tips..Thank You :)

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'?

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.

Great hints!

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". :)

That was quick and interesting. Knew most, some I did not.

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 .

Great, Thank You!🤗

Real useful tips.👍

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.

Excellent. Thanks

=SUM(C16:C45)/2 this also much easy and one step . thank you

Awesome... Thanks....❤

Very nice explanation Pal

Super.. thank you.

Valuable.. thanks..

Brilliant!

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 .

cool. thank you

Thanks

You won the bet!

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

Nice video 📹 👍

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

Also like the sum if function to work with check registers and update a sheet for budget performance

Nothing new to me but, nice to have the collection in 1 place. 👌

Thank you. This is useful. Can you clarify how you repeated the first cell for the running total formula?

You won the bet! :-)

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.

Nice

EXCELlent SUMmary!

Wonderful for lazy people as I am ! Thank you.

Very helpful. Thanks Please make video on VLOOKUP function

Very nice 👌..can I take your advice ..

Always wondered if it is possible to use SUM in an array formula. I like to use arrays.

The times I have added a summary sheet and then gone through all the worksheets to add another cell 🤦‍♂ - Saved me again....

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.

Hi, Does Power query support appending of data from .xls files?

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.

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.

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

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 😊

Ps _ there's also a 'cntrl_shift enter' version.

OMG

Hey - this lecture do not meet your reputation - too entry level - Raghu

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