How To Use The Excel Sum Function

How To Use The Excel Sum Function

SUM function is probably the most used function of Excel. It is also really easy to use.

Syntax is:

=SUM(number1,[number2],…)

Examples:

• =SUM(C2,C3,C5,C6)
• =SUM(B1:B4)
• =SUM(A1:C4)

You can add in Excel also in several other ways. Excel will mark the safest area you want to sum up, along with the cell below or to the right where you want the sum. Just click on the icon bar or by press keyboard shortcut Alt + =

Ten Examples of Using SUM Function

Open Excel and save your file as sum.xlsx.

The simplest example of SUM

To use the SUM function in its simplest form, click the cell E1 and enter the formula

=SUM(50,100,25) and press the Enter key. Now your screen will look like this:

If you look at the formula bar, you can see the SUM function and in the E1 cell you can see the result 175 which is equal to the sum of 50, 100 and 25. Here, you have used comma (,) to separate the numbers inside the SUM function.

SUM of simple range

Enter a few numbers in the cells from A1 to A10. You can enter not only positive whole numbers, but also negative numbers or decimal numbers as well. After entering the values, your screen will look like this. (Of course the values will be different).

Suppose you want to calculate the sum of all the ten numbers. Click in the cell E2 and enter the formula

=SUM(A1:A10). Now your screen will look like this:

The sum of the ten numbers is 189.4. Here, you have used colon (:) to select a number of consecutive cells, cells from A1 to A10.

SUM of three cells

Suppose you want to add only the negative numbers in the cells. Click in the cell E3 and enter the formula

=SUM(A2,A4,A9). Now your screen will look like this:

Here the result is -176. Here, you have used comma (,) to separate the cell references inside the SUM function.

SUM of ranges of cells

Suppose you want to add numbers in cells A3, A4, A5, A7 and A8 and then add 40 to the result. Click the cell E4 and enter the formula

=SUM(A3:A5, A7:A8, 40). Now your screen will look like this:

Here the result is 88.4. We have used comma and colon properly to calculate the sum.

SUM and LARGE functions

Suppose you want to calculate the sum of the 3 largest numbers in the list. Click the cell E5 and enter the formula =SUM(LARGE(A1:A10,{1,2,3})). Now your screen will look like this:

Here the result is 228. If you analyze the values in cells A1 to A10, you could find that the three largest numbers are 99, 79 and 50. We have used the LARGE function inside the SUM function to get the three largest numbers in the list.

SUM and SMALL functions

Suppose you want to calculate the sum of the 4 smallest numbers in the cell. Click the cell E6 and enter the formula =SUM(SMALL(A1:A10,{1,2,3,4})). Now your screen will look like this:

Here the result is -164. If you analyze the values in cells A1 to A10, you could find that the four smallest numbers are -89, -62, -25 and 12. We have used the SMALL function inside the SUM function to get the four smallest numbers in the list.

SUM of other SUM

Suppose for each cell, you want to calculate the sum of the current cell and the next cell. For example, for A1 the result should be A1 + A2, for A2 the result should be A2 + A3 and so on. Click the cell B1 and enter the formula =SUM(A1,A2). Copy the formula in B1 to cells B2 to B10. Now your screen will look like this:

Here you could find that the value in cell B10 is 99 because the value in A11 is 0 and hence =SUM(A10,A11) gives 99 itself.

SUM and logical values

Click the cell E7 and enter the formula =SUM(99,TRUE). Now your screen will look like this:

Here the result is 100 and the logical value TRUE is translated into number 1.

SUM of data in other sheet

Suppose you want to calculate sum of two numbers where the first number is in Sheet 1 and the second number is in Sheet 2. Enter a number; say 28, in the cell A1 of Sheet 2. Click cell E8 and enter the formula =SUM(A1,Sheet2!A1). Now your screen will look like this:

SUM and strings in Excel

Click the cell E9 and enter the formula =SUM(20, 50, “5”). Now your screen will look like this:

Here the string value 5 is translated into a number.

Template

Further reading: 
Basic concepts 
Getting started with Excel
Cell References