Site icon Professor Excel

SUM in Excel: 8 Simple Ways Of Adding Up Values!

sum, sum up, excel, add, addition

One of the basic applications in Excel is summing up values. The most popular ways of adding up numbers are just using the ‘+’ sign or the formula SUM. But there are many other methods. Do you know about these 6 other ways to sum up values in Excel? In this article we’ll take a look at 8 methods for summing up values in Excel and compare them.

8 methods in detail

Let’s start by taking a look at each method to create sums in Excel separately. After that we will compare them by their most important characteristics.

01. The most popular way: SUM()

The SUM() formula is extremely easy to use: Just type =SUM() into an empty cell or press the sum button on the right hand side of the Home ribbon. Within the brackets you’ve got several options:

  1. Write the “hard coded” values (although not recommended), e.g. =SUM(3,5). The return value will be 8.
  2. Instead of hard coded values you can also refer to other cells, e.g. =SUM(A1,A2). The sum of the values in these two cells will be returned. Also getting sums of larger cell ranges is possible: =SUM(A1:A5) will return of all cells A1, A2, A3, A4 and A5.

Please note that the sum of all cells within the given cell range will be added. If you hide cells in between or cells are invisible because you’ve set a filter, they are still regarded.

02. The easiest way: ‘+’ sign

Very intuitive: Using the ‘+’ sign. Like the SUM() formula, you got the two options of combining two values with the ‘+’ sign (e.g. =3+5) or cell references (e.g. =A1+A2).

Please note that the ‘+’ sign can’t regard cell ranges but only single cells or values. That fact makes this method more troublesome but also more stable at the same time: As you have to select any input cell separately, you won’t have the problem that you accidentally regard unwanted values when inserting rows or columns in between.

03. The fastest way: Checking the status bar

The fastest way to see a sum by looking at the status bar.

If you select data in Excel, usually a summary of the selected data is already shown in the status bar. It’s located on the bottom of the window on the right hand side. 

If there is no sum shown, check these two options:

  1. Is your data really numeric values? Or is it formatted as text? This article has more information on forcing text cells to a number format.
  2. Have you defined that you want to see the sum in the status bar? Right click on it and check if ‘Sum’ is ticked. If not, click on it for setting the tick.

04. The old way: SUMIF or SUM(IF())

Examples for SUM, SUBTOTAL, SUMPRODUCT and SUM(IF())

Before there was the SUMIFS formula, Excel provided a similar way for adding values under a condition: SUMIF (without ‘s’ in the end). SUMIF works more a less the same way as SUMIFS but can only regard one condition at a time.

If you are familiar with SUMIFS, we recommend only using SUMIFS from now on. If you are not familiar with SUMIFS continue with the next method – and forget about SUMIF…

Some more words about =SUM(IF()). With such array formula you can also sum up values under a condition. Let’s say, we want to know the sum of the prices of all blue cars in the picture on the right hand side. The formula would look like this:

{=SUM(IF(B3:B8="Blue",C3:C8,0))}

Type the formula without the curly brackets. Instead of just pressing enter press Ctrl + Shift + Enter. That way, you can also combine conditions by adding another IF formula within the first IF formula. 

05. The new and better way: SUMIFS

SUMIFS is only available since Excel 2007 – but nowadays most people use Excel 2007 or above. SUMIFS sums up all values under one or more conditions. It can be used for getting a sum of values but also for just looking up values – as long as those values have a numeric format. Please refer to this article for more information on how to use the SUMIFS formula.

06. The complicated way: SUMPRODUCT

SUMPRODUCT can sum up values as well. But it can do much more than just returning sums:

As the usage of SUMPRODUCT is comparatively difficult, it’s recommended to use other ways:

07. The ‘only visible’ way: SUBTOTAL

SUBTOTAL has two options: Regarding all cells or only visible cells. Now we concentrate on the only visible cells as it is the major difference to the other methods. Hidden cells – for example because they’ve been filtered – won’t be regarded. There are a number of possible calculation methods and one of them is summing up the values.

The SUBTOTAL formula has two parts (please compare to number 2 in the image above):

  1. The type of calculation. For summing up values just type 9 (if you want to regard all cells) or 109 for the only visible cells.
  2. The cell references containing the values to be added. There can be hidden cells, rows or column within this range – they won’t be regarded in the result.


Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!


08. The Pivot Table

If you just want to get a simple sum, a Pivot Table might be a little bit over the top. But anyway, a Pivot Table can add up values. Actually, with a Pivot Table you can do many more things:

We’ve already published various articles about Pivot Table:

Comparison

After getting to know each method in detail, we can compare them. The criteria are:

Comparison of methods to sum up values in Excel.
Exit mobile version