5 Excel SUM Functions Tips you MUST KNOW - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Bart
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
reply
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
reply
Thor
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.
reply
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.
reply
it_industry
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)
reply
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)
reply
Lindsay
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'?
reply
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'?
reply
Shayan
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 .
reply
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 .
reply
Emre
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.
reply
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.
reply
Jonathan
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.
reply
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.
reply
Marty
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.
reply
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.
reply
David
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.
reply
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.
reply
Nazeerul
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
reply
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
reply
Add a review, comment
Other channel videos
