Ajay Anand

Different ways to find SUM in Excel

This blog post is about 10 different ways to find the Sum in Excel.

We have a list of 5 numbers in the cells B3, B4, B5, B6 and B7 as shown below.

Now let me show you the most common and uncommon methods for finding Sum in Excel.

Using Addition Operator ‘+’

=B3+B4+B5+B6+B7

SUM function

SUM function is one of 10 most used functions in Excel and its purpose is generating Sum of values supplied into it.

There are two ways to supply numbers into SUM function.

  • Formula when numbers are supplied as individual cells
=SUM(B3,B4,B5,B6,B7)
  • Formula when numbers are supplied as individual cells
=SUM(B3:B7)

There are two different methods to create AutoSUM in Excel.

  • Button for AutoSUM

Select the cell just below the last cell containing number > in the Home tab > click on the Σ button placed in the category called Editing

  • Keyboard shortcut for AutoSUM

Alt + = is the keyboard shortcut for AutoSUM in Excel

SUBTOTAL Function

SUBTOTAL function in Excel can return the aggregate result of values supplied into it. To find the Sum of values in a column, use 9 as the first argument and the data range containing numbers as the second argument of SUBTOTAL function.

=SUBTOTAL(9,B3:B7)

AGGREGATE Function

AGGREGATE function is SUBTOTAL function + the option to ignore Error values.

To find the Sum of values in a column, use 9 as the first argument, any number from 0 to 7 as the second argument and the data range containing numbers as the second argument of AGGREGATE function.

=AGGREGATE(9,3,B3:B7)

SUMPRODUCT Function

The SUMPRODUCT function in Excel multiplies the ranges supplies into it and returns the sum of products. When supplied with a single column of numbers, SUMPRODUCT will return the sum of those numbers.

=SUMPRODUCT(B3:B7)

SUMIF Function

SUMIF function in Excel is generally used to find the sum of values based on a condition. But following is how we can use the SUMIF function to add numbers in a column.

=SUMIF(B3:B7,"<>0",B3:B7)

PivotTable to find Sum

Create a PivotTable from the data range containing numbers, then drag and drop field called Data (Column containing numbers) into the area for Values to generate the Sum.

VBA to find the Sum of Values in a selection.

Use the following VBA code to find the Sum of values in the selected cells.

Sub FindSum()
Dim myRange As Range
Dim mySum As Long
Set myRange = Selection
mySum = WorksheetFunction.Sum(Range(myRange.Address))
MsgBox mySum
End Sub