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

This article will demonstrate comprehensively how the SUM function works in Excel, both on its own and in conjunction with other Excel functions.

Overview of the SUM Function

Summary

Adds all the numbers in given ranges of cells.

Syntax

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

Arguments

ARGUMENT	REQUIREMENT	EXPLANATION
number1	Required	The first value to sum.
[number2]	Optional	The second value to sum.
[number3]	Optional	The third value to sum.

Note:

If arguments contain errors, SUM will throw an error.
SUM automatically ignores empty cells and cells with text values.
This function can take 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

To explain our examples, we’ll use the following sample dataset. Initially, all the cells are in General format except the Price columns which are in Accounting format.

Example 1 – Summing a Range

Let’s calculate the Total Net Price of all the items using the SUM function.

Steps:

Enter the following formula in cell E10:

=SUM(E5:E9)

Press Enter to perform the sum operation.

Example 2 – Summing an Entire Column

If we use a column as an argument, the SUM function will calculate the sum of all the numeric elements stored in that column.

Steps:

Enter the following formula in cell D10:

=SUM(E:E)

Press Enter to get the column sum.

Example 3 – Summing Non-Contiguous Cells

Now let’s get the sum of some specific Total Price values by using their cell references as arguments in the SUM function.

Steps:

Enter the following formula in cell E10:

=SUM(E5,E7,E9)

Press Enter to perform the sum operation.

Example 4 – Using AutoSum Feature

Excel provides an option named AutoSum to make our calculations easier. Let’s use AutoSum to calculate the Total Net Price for our dataset.

Steps:

Select cell E10.
Go to the Formulas tab and click on AutoSum.

Press Enter to return the sum of the values in the column above.

Example 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. For example, let’s calculate the price sum of every third row in our dataset.

Steps:

Enter the formula below in cell E10:

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

Press Enter to return the sum for the specific criteria, or CTRL + SHIFT + ENTER if not using Office365.

How Does the Formula Work?

This is an array formula, which is why CTRL + SHIFT + ENTER is required for non-Office365 users.
(MOD(ROW(E4:E12),3)=0): Finds the rows which are divisible by 3 (3, 6, 9 etc.) by using the ROW function.
=SUM(E5:E9*(MOD(ROW(E5:E9),3)=0)): Sums the totals of the selected rows.

Example 6 – Summing the Largest Three Values

Suppose we want to find out the sum of the three top prices from the dataset.

Steps:

Enter the formula below in cell E10:

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

Execute the sum by pressing the Enter key.

How Does the Formula Work?

LARGE(E5:E9,{1,2,3}): The LARGE function returns the largest values from the E5:E9 range, and {1,2,3} defines the first 3 values in the selected data in that order, for example $500.00, $300.00, $242.00.
SUM(LARGE(E5:E9,{1,2,3})): Calculates the sum of the selected three values.