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.


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.

How to Use the SUM Function in Excel: 6 Easy Examples

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.


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


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.

Read More: Excel SUM with OFFSET & MATCH Functions


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.

Download Practice 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.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo