In continuation to our previous tutorial on MIN and MAX functions in Excel, we now move on to another important basic function, the SUM function. You will get to learn about the SUM function in Excel on real datasets.
- What is the Sum function in Excel?
- Formula of the SUM function
- How to Apply the SUM Function? Example 1
- How to Apply the SUM Function? Example 2
- How to Apply the SUM Function using AutoSum? Example 3
- How to Apply the SUM Function using AutoSum? Example 4
- How to Apply the SUM Function using AutoSum? Example 5
- How to Apply the SUM Function? Example 6
- Important to Note
- Related Tutorials
What is the Sum Function in Excel?
The Sum function in MS Excel allows you to add multiple numbers stored in different cells. It is categorized under Excel Math and Trigonometry functions and is one of the most basic, simple, and commonly used MS Excel functions. The “Sum” icon allows you to add a series of data stored in a row or column in a very agile way.
Formula of the SUM function
The formula of the SUM function is –
=SUM(number1, [number2], [number3]……)
Number1 (necessary argument) – The first item we want to sum.
Number2 (necessary argument) – The second item we want to sum
Number3 (optional argument) – The third item we want to sum
These constant, cell reference or array containing numeric values are either entered manually or present in the reference cell.
How to Apply the SUM Function? Example 1
Below is the screenshot from the personal monthly expenditure sheet. I have added the data for bills and utilities for every month.
To sum up the data, we will go through the following steps –
Add the Function
In the cell where you want to add the numbers, start writing =SUM(
Select the Data
You can now select the data you want to be added. In this case, the selected cell numbers are from B2 to B13.
Close the Parenthesis
The result will be displayed in the chosen cell.
How to Apply the SUM Function? Example 2
You can use the SUM function to non-contiguous cells. Non-contiguous means non-adjoining cells. Here
- Apply the formula =SUM(
- Select cells that you want to sum
- Close the parentheses
You get the result.
How to Apply the SUM Function using AutoSum? Example 3
This is interesting. Now we will sum up using the Autosum function. Let’s use the same dataset we used above in Examples 1 and 2.
Click on the cell where you want your result to appear, here B14.
Hover your cursor on AutoSum. Click on the drop-down, you will find Sum, along with other functions like Average, Count Numbers, Min, Max, etc.
Now just click on Sum, your targeted cell will now have the added data. The Sum function within AutoSum automatically calculates the data for the entire column.
How to Apply the SUM Function using AutoSum? Example 4
Now let’s check if we can use AutoSum to sum multiple columns. Below we have data in multiple columns, now, we click on Sum function within AutoSum and get the data for column B.
We get the total sum for Column B. Now, we will hold the cell in which we received the sum and drag it horizontally. In this case, Cells B14 to D14.
We see that the consecutive columns are automatically filled. We can do this for even bigger datasets that need to be calculated for numeric values horizontally.
How to Apply the SUM Function? Example 5
Now let’s check if we can use the SUM function from AutoSum for non-contiguous cells. Here we have marked Cell A14 to fetch data for Totals (Bills & Utilities and Personal). We will then move on to Cell B14 and click on AutoSum to apply the SUM formula for Columns B and D.
When you apply AutoSum in Cell B14, it will fetch the entire numeric values from Column B. You can then move on to the next column that you want to add.
You can see that both columns are different color-coded for demarcation. Column B in blue and Column D in red. Now in Cell B14, you have the data from both Columns B and D. You can either click on AutoSum again or just click Enter.
Now to calculate Totals (Food & Dining, Auto & Transport, and Health & Fitness), i.e. Cell A15, we will follow the same process and will get our total data in B15.
Here the Sum function would automatically return to Column B, but you can move on and select the columns you wish to calculate. Here we have calculated the data from Columns C, E, and F, with Column C, marked blue, E marked red, and F, purple.
This way, we can sum multiple columns. To sum the entire data set, we can similarly sum all the columns.
How to Apply the SUM Function? Example 6
Now we add a new column here, which is Column G. this will calculate the total monthly expenses.
We can click on AutoSum or add =SUM(
Here, I will be adding the =SUM(number1, [number2], [number3]……) function.
Now hold the Cell G2 and drag vertically.
Now you have the data for every month without having to work on individual cells. Do give it a try on your own MS Excel sheet.
Important to Note
- Arguments can be constants, ranges, named ranges, or cell references
- SUM can handle up to 255 total arguments
- SUM ignores empty cells and cells with text values
- In case there are any error in the arguments, the SUM function will return an error
- The AGGREGATE function can be used to ignore errors and perform sum
Hope this tutorial on the SUM function in Excel helped to understand how you can use it to work on your datasets. Stay with us for more such tutorials in the future.
Top Trending Articles:
Data Analyst Interview Questions | Data Science Interview Questions | Machine Learning Applications | Big Data vs Machine Learning | Data Scientist vs Data Analyst | How to Become a Data Analyst | Data Science vs. Big Data vs. Data Analytics | What is Data Science | What is a Data Scientist | What is Data Analyst