I know you’re probably thinking that everyone knows how to use the SUM function, but I’m willing to bet that most people don’t know all the shortcuts and tricks I’m about to show you. These are SUM function tricks you can use every day, and some also apply to all Excel functions.
Watch the Video
Enter your email address below to download the sample workbook.
Excel SUM Function Tricks
Trick 1: AutoSum
AutoSum detects the range you want to sum by looking for rows/columns adjacent to the cell you have selected that contain numbers. You’ll find it on the home tab:
However, I find the keyboard shortcut ALT+= easier to use.
Tip: Select the whole table plus a blank row and column for the sum formulas and ALT+= to automatically enter the SUM formulas for each row and column:
Bonus tip: you can select non-contiguous ranges and press ALT+= to insert multiple AutoSums – see video for demo.
Trick 2: CTRL shortcut for non-contiguous ranges
If you want to sum non-contiguous cells, hold the CTRL key while you select the cells with your mouse to have Excel automatically insert the comma separators for you. This works for other functions too.
Bonus tips not in the video from Bob Umlas - AutoSum Subtotals: To insert a Grand Total for a range of cells containing sub-totals, select the whole range including the cell you want your grand total in > ALT+=
Or simply SUM the entire range and divide by 2 e.g. using the example data above in cell C47 enter this formula:
Trick 3: Running Total
To insert a running total reference the first cell in the range, then Absolute reference the first cell in the range before copying the formula down:
Trick 4: SHIFT for 3D ranges
Sum multiple contiguous sheets by selecting the cell on the first sheet and while holding the SHIFT key, select the last sheet in the range:
Caution: This will sum all sheets from Jan to Dec. If you drag another sheet inside this range, it will also be included in the sum.
Trick 5: Insert multiple SUM formulas in one go with CTRL+ENTER
Select all the cells you want your SUM formulas in, then enter the first formula and CTRL+ENTER to complete the formula:
If you have a SUM tip or trick, please share it in the comments below.