10 AutoSum and Sum Formula Tricks

Taking a total of cells in Excel is what everyone knows how to do it. But that simple Sum Function can be used in many intriguing ways and for various purposes

Today I have 10 such tricks for you! Check out how many are new for you!

 

Trick #1 Auto sum

1. Whenever you want to take a total (horizontally or vertically)
2. Just select the cell and press Alt =
3. The sum function will be applied and it will guess the range that you want to total. If the range is correct, press enter 🙂

 

Trick #2 Auto sum multiple cells

1. Select multiple cells that you want to total
2. Use the shortcut Alt =
3. The sum automatically appears

 

Trick #3 Auto sum around a range

1. Simply select the entire range with one row and one column extra
2. Use the shortcut Alt =
3. And Boom, you have the totals all across the range

 

Trick #4 Sum with sheet range

This will take a total from Sheet 4 to Sheet 2 of the cell A1 on each sheet

 

Trick #5 Sum of sheets starting with “J”

Here is the formula used =SUM(‘J*’!A1) A few things to note

1. ‘J’ is between 2 apostrophe signs, it is not a double quote (“)
2. ‘J*’ can be replaced with any other letter. Asterisk means anything after J
3. The formula gets automatically revised when you press enter and this is what excel shows =SUM(Jan!A1,June:July!A1)
4. In the same scenario, if you want to take a total of a range (instead of a single cell) use this formula =SUM(‘J*’!A1:A10)
5. If you are writing this formula in the Sheet named Jan it will exclude Jan and will consider only June and July. So for this formula to work properly put it in a different sheet

 

Trick #6 Total of Filtered Rows

1. If you want the Total to update when you filter the rows use SUBTOTAL Function instead of the SUM function
2. =SUBTOTAL(9, Range_to_total)
3. The number 9 stands for only summing visible rows when the filter is applied

 

Trick #7 Sum Visible Rows

1. Note that rows from 10 to 21 are hidden manually and not with a filter
2. When ever you work with manually hidden rows use this formula =SUBTOTAL(109, Range_to_total)
3. The number 109 stands for only summing visible rows especially when the rows are hidden manually

 

Trick #8 Grand Totals of Sub Totals in a single range

1. When you are trying to take a Grand Total of multiple subtotals use the subtotal formula everywhere
2. Note that Emp Level sum is also done using Subtotal
3. And the Grand Total is also done using Subtotal. This avoids totaling the cells twice

 

Trick #9 Running Totals

For running totals, simply freeze (lock) the first part of the Sum range and then drag the formula down

 

Trick #10 Ignoring Errors in a Sum Range

1. To ignore error values, use the Aggregate function
2. The number 9 Stands for summing
3. The number 6 stands for ignoring error values

 

How many tricks did you Check?

How many tricks did you know it already? Along side share your most helpful auto sum, sum formula and its variation tricks!

 

Some more productivity tricks

1. 10 Productive Excel Habits
2. Custom Formatting Tricks
3. 16 Time Saving Pivot Table Tricks
4. 30 Evergreen productivity hacks for everyday use of Excel