

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.
Contents
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.
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:
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.
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.
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:
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.
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.
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:
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):
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:
After getting to know each method in detail, we can compare them. The criteria are:
Comparison of methods to sum up values in Excel.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.