# How to Use SUM Function in Excel (With 6 Easy Examples) MS Excel provides a most useful function called SUM for summation purposes. Formula with this function automatically updates with the addition or deletion of a value. It also combines the changes made to a current cell range. This article will share the complete idea of how the SUM function works in Excel autonomously and then with other Excel functions.

SUM Function in Excel (Quick View) ## Excel SUM Function: Syntax & Arguments Summary

Adds all the numbers in each range of cells.

Syntax

=SUM (number1, [number2], [number3], …)

Arguments

Argument Required or Optional Value
number1 Required Pass the first value to sum.
[number2] Optional Pass the second value to sum.
[number3] Optional Pass the third value to sum.

Note:

• If arguments include errors, SUM will throw an error.
• SUM automatically ignores empty cells and cells with text values.
• This function can manipulate up to 255 total arguments.
• Arguments can be supplied as constants, ranges, named ranges, or cell references.

## How to Use the SUM Function in Excel (6 Examples)

### Example 1: Calculate Total Using Sum Range

Let’s have a dataset of some foods with their name, quantity, unit price, net price. Now we will calculate the total price of the foods using the SUM function. Step 1: Enter the formula in cell E14 and press Enter

`=SUM(E4:E12)`

Formula Explanation

• Here E4:E12 this is the range where all our prices are stored, and the sum will be executed on this. ### Example 2: Calculate Sum of Entire Column Using SUM Function

Instead of using a specific range, we can use a whole column for adding. If we use a column as an argument the SUM function will calculate the sum of all the elements stored in that column.

Let’s do the using the previous dataset.

Step 1: Enter the formula in cell E14 and press Enter

`=SUM(E: E)`

Formula Explanation

• E: E means the whole column range of the E column. ### Example 3: Calculate Sum of the Non-Contiguous Cells

Now lets we want to get a sum of some selected foods. For this, we can the cell references of that cells in the SUM function’s argument. Our task is to find out the total price of the marked foods price.

Step 1: Enter the formula in cell E14 and press Enter

`=SUM(E4,E7,E9,E11)`

Formula Explanation

• Here E4 contains the price for Burger, E7 contains the price for Sausage, E9 contains the price for the Fries, E11 contains the price for Candy. ### Example 4: Calculate Sum using AutoSum

Excel provides an option named AutoSum to make our calculation easier. Now here we will use AutoSum to calculate the total sum for the previous dataset.

Step 1: Go to the cell where you want to print the result Step 2: Now select the AutoSum option from the Editing section which is under the Home tab Step 3: Now press Enter ### Example 5: Calculate Sum of Every Nth Row

Using the SUM function in the formula we can calculate the sum of every Nth row in a dataset. N could be 1,2,3,4…… Now we will calculate the price sum of every third row of the previous dataset. Step 1: Enter the formula in cell E14 and press CTRL + SHIFT + ENTER

`=SUM(E4:E12*(MOD(ROW(E4:E12),3)=0))`

Formula Explanation

• This is an array formula that’s why you need to press CTRL + SHIFT + ENTER
• (MOD(ROW(E4:E12),3)=0) this will find the rows which are divisible by 3 like 3,6,9 etc.
• Lastly, the SUM function will count the total summation of the selected rows.
• To explore more about the ROW function you can visit this link:
How to Use ROW Function in Excel (With 8 Examples) ### Example 6: Calculate Sum of Three Largest Numbers

Let’s say we want to find out the sum of the three top prizes from the dataset. Here we will do that using the SUM function. Step 1: Enter the formula and press CTRL + SHIFT + ENTER

`=SUM(LARGE(E4:E12,{1,2,3}))`

Formula Explanation

• LARGE(E4:E12,{1,2,3}) here LARGE function is used to find out the large values from the E4:E12 range, and {1,2,3} is defining the first 3 values from the selected data. Like \$500.00, \$300.00, \$242.00 etc.
• Finally, the SUM function calculates the sum of the selected three values.
• If you are interested to explore more about the LARGE function you can check this link:
LARGE Function in Excel ## The Limitations of the SUM Function

• The cell range provided should meet the dimensions of the source.
• The cell including the output must always be formatted as a number

## Things to Remember

Common Errors When they show
#NAME This error will show if the argument is not properly passed.
#VALUE! This will appear if there is any #VALUE! Error containing cells in the passing range.
#REF! This error will happen if a SUM formula is used between different workbooks and closed the source workbook.

## Conclusion

This is all about the SUM function and its different applications. Overall, in terms of working with time, we need this function for various purposes. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any other method of utilizing this function, then please feel free to share it with us.  