Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


Download Practice Workbook


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.

Excel SUM Function


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)

using sum function in excel for a range

  • Then, press Enter and this will perform the sum.

Read More: 51 Mostly Used Math and Trig Functions in Excel


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)

sum function in excel using entire column

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

Read More: 44 Mathematical Functions in Excel (Download Free PDF)


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)

using sum function in excel for non-contiguous cells

  • 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.

using sum function in excel in 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.


Related Articles

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo