Learn more about SUM

To quickly obtain the sum of a range of cells, select the range and see the sum displayed in the lower right-hand side of the Excel window. 

The Status Bar displays information regarding whatever is currently selected, whether a single cell or multiple cells. Right-click on the Status Bar to see a list of available options. It will also display values for your selected range if you have those attributes checked. 

The AutoSum Wizard lets you easily add a SUM formula to your worksheet. Select an empty cell directly above or below the range to be summed. Then on the Home or Formula tab on the Ribbon, select AutoSum > Sum. The AutoSum Wizard will automatically sense the range to be summed and build the formula for you. It can also work horizontally if you select a cell to the left or right of the range to be summed. It will not work on non-contiguous ranges. Learn more about that in subsequent content here. 

The AutoSum dialog also enables you select these other common functions:

• Average

• Count numbers

• Max

• Min

• More functions

AutoSum vertically

In this example, the AutoSum Wizard has automatically detected cells B2:B5 as the range to be summed. All you need to do is press Enter to confirm it. To add or exclude cells, you can hold the appropriate Shift > Arrow key until your selection matches what you want. Then press Enter.

Intellisense function guide 
In the image, note the SUM(number1,[number2], …) floating tag beneath the function. This is its Intellisense guide. If you click the SUM or function name, it will turn into a blue hyperlink that can take you to the Help topic for this function. If you click the individual function elements, their representative pieces in the formula will be highlighted. In this case only B2:B5 would be highlighted since there is only one number reference in this formula. The Intellisense tag will appear for any function. 

AutoSum horizontally 

The AutoSum Wizard work simply for contiguous ranges. If there is a blank row or column within your sum range, Excel selection will stop at the first gap.
In that case you’d need to SUM by selection, where you add the individual ranges one by one. In this example if you had data in cell B4, Excel would generate =SUM(C2:C6) since it would recognize a contiguous range.

You can quickly select multiple, non-contiguous ranges with Ctrl+Left Click. First, enter “=SUM(“, then select your different ranges and Excel will automatically add the comma separator between ranges for you. Press enter when you’re done.

TIP: you can use ALT+ = to quickly add the SUM function to a cell. Then all you need to do is select your range(s).

Note: you may notice how Excel has highlighted the different function ranges by color, and they match within the formula itself, so C2:C3 is one color, and C5:C6 is another. Excel will do this for all functions, unless the referenced range is on a different worksheet or in a different workbook. For enhanced accessibility with assistive technology, you can use Named Ranges, like “Week1”, “Week2”, etc. and then reference them in your formula:

=SUM(Week1,Week2)

1. You can absolutely use SUM with other functions. Here’s an example that creates a monthly average calculation:

   

   • =SUM(A2:L2)/COUNTA(A2:L2)

2. Which takes the SUM of A2:L2 divided by the count of non-blank cells in A2:L2 (May through December are blank).

1. Sometimes you need to sum a particular cell on multiple worksheets. It may be tempting to click on each sheet and the cell you want and just use “+” to add the cell values, but that’s tedious and can be error prone.

   • =Sheet1!A1+Sheet2!A1+Sheet3!A1

   You can accomplish this much easier with a 3D or 3-Dimensional SUM:

   

   • =SUM(Sheet1:Sheet3!A1)

   Which will sum the cell A1 in all sheets from Sheet 1 to Sheet 3.

   This is particularly helpful in situations where you have a single sheet for each month (January-December) and you need to total them on a summary sheet.

   

   • =SUM(January:December!A2)

   Which will sum cell A2 in each sheet from January through December.

   Notes: If your worksheets have spaces in their names, like “January Sales”, then you need to use an apostrophe when referencing the sheet names in a formula. Notice the apostrophe BEFORE the first worksheet name, and again AFTER the last.

   • =SUM(‘January Sales:December Sales’!A2)

   The 3D method will also work with other functions like AVERAGE, MIN, MAX, etc:

   • =AVERAGE(Sheet1:Sheet3!A1)

   • =MIN(Sheet1:Sheet3!A1)

   • =MAX(Sheet1:Sheet3!A1)

You can easily perform mathematical operations with Excel on their own, and in conjunction with Excel functions like SUM. The following table lists the operators that you can use, along with some related functions. You can input the operators from either the number row on your keyboard, or the 10-key pad if you have one. For instance, Shift+8 will enter the asterisk (*) for multiplication.

Operator	Operation	Examples
+	Addition	=1+1 =A1+B1 =SUM(A1:A10)+10 =SUM(A1:A10)+B1
-	Subtraction	=1-1 =A1-B1 =SUM(A1:A10)-10 =SUM(A1:A10)-B1
*	Multiplication	=1*1 =A1*B1 =SUM(A1:A10)*10 =SUM(A1:A10)*B1 =PRODUCT(1,1) - PRODUCT function
/	Division	=1/1 =A1/B1 =SUM(A1:A10)/10 =SUM(A1:A10)/B1 =QUOTIENT(1,1) - QUOTIENT function
^	Exponentiation	=1^1 =A1^B1 =SUM(A1:A10)^10 =SUM(A1:A10)^B1 =POWER(1,1) - POWER function

For more information, see Use Excel as your calculator.

Other Examples

1. Let’s say you want to apply a Percentage Discount to a range of cells that you’ve summed.

   

   • =SUM(A2:A14)*-25%

   Would give you 25% of the summed range, however that hard-codes the 25% in the formula, and it might be hard to find later if you need to change it. You’re much better off putting the 25% in a cell and referencing that instead, where it’s out in the open and easily changed, like this:

   • =SUM(A2:A14)*E2

   To divide instead of multiply you simply replace the “*” with “/”: =SUM(A2:A14)/E2

2. Adding or Subtracting from a SUM

   i. You can easily Add or Subtract from a Sum using + or - like this:

   • =SUM(A1:A10)+E2

   • =SUM(A1:A10)-E2