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

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.

Download the Practice WorkBook

SUM Function in Excel (Quick View)

Overview of SUM function

Excel SUM Function: Syntax & Arguments

syntax and argument of sum function

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.

Calculate Total Using Sum Range

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.

Enter formula using SUM function

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.

Enter the formula using Sum function for entire 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.

Calculate sum of the non-contiguous cells

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.

Enter formula using sum function

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

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

select autosum

Step 3: Now press Enter

Press enter to see autosum result

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.

Calculate sum of every nth row

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)

Enter formula using SUM MOD and ROW function

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.

Calculate Sum of Three Largest Numbers

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

Enter formula using SUM and LARGE function

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.


Further Readings

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