How to Use SUM Function in Excel (6 Easy Examples)

Get FREE Advanced Excel Exercises with Solutions!

MS Excel provides a most useful function namely the SUM function 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.

Table of Contents hide

Download Practice Workbook

Overview of Excel SUM Function

6 Easy Examples to Use the SUM Function in Excel

1. Using Sum Range

2. Using Entire Column

3. Summing Non-Contiguous Cells

4. Using AutoSum Feature

5. Summing Every Nth Row

6. Summing Largest Three Values

Limitations of the SUM Function

Conclusion

Download Practice Workbook

SUM Function.xlsx

Overview of Excel SUM Function

Summary

Adds all the numbers in each range of cells.

Syntax

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

Arguments

ARGUMENT	REQUIREMENT	EXPLANATION
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.
The SUM function 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.

6 Easy Examples to Use the SUM Function in Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 4 columns. Initially, we are keeping all the cells in General format and the price columns in Accounting format. For all the datasets, we have 4 unique columns which are Food, Quantity, Unit Price, and Net Price. Although we may vary the number of columns later on if that is needed.

1. Using Sum Range

Let’s consider the dataset of some foods with their name, quantity, unit price, and net price. Now we will calculate the total price of the foods using the SUM function in excel.

Steps:

First, enter the following formula in cell E10.

=SUM(E5:E9)

Then, press Enter and this will perform the sum.

2. Using Entire Column

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 this using the previous dataset.

Steps:

To begin with, enter the formula in cell D10:

=SUM(E: E)

Now, press the Enter key to get the column sum.

3. Summing Non-Contiguous Cells

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

Steps:

To start with, insert the formula below in cell E10:

=SUM(E5,E7,E9)

Finally, press Enter to confirm the sum operation.

Similar Readings

4. Using AutoSum Feature

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.

Steps:

For this, select cell E10.
Next, go to the Formulas tab and click on AutoSum.

Now, when you press Enter, you should get the sum of the above values.

5. Summing 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……, etc. Now we will calculate the price sum of every third row of the previous dataset.

Steps:

To begin with this method, type in the formula in cell E10:

=SUM(E5:E9*(MOD(ROW(E5:E9),3)=0))

Finally, press the Enter key and you should get the sum for the specific criteria.

🔎 How Does the Formula Work?

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 using the ROW function.
=SUM(E5:E9*(MOD(ROW(E5:E9),3)=0)): This part will count the total summation of the selected rows.

6. Summing Largest Three Values

Let’s say we want to find out the sum of the three top prices from the dataset. Here we will do that using the SUM function.

Steps:

Firstly, enter the formula below in cell E10:

=SUM(LARGE(E5:E9,{1,2,3}))

After that, confirm the sum by pressing the Enter key from the keyboard.

🔎 How Does the Formula Work?

LARGE(E5:E9,{1,2,3}): Here the LARGE function will find out the large values from the E5:E9 range, and {1,2,3} is defining the first 3 values from the selected data. Like $500.00, $300.00, $242.00 etc.
SUM(LARGE(E5:E9,{1,2,3})): Finally, the SUM function calculates the sum of the selected three values.

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.

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.

How to Use SUM Function in Excel (6 Easy Examples)

Download Practice Workbook

Overview of Excel SUM Function