SUM Cells in Excel (Table of Contents)
Introduction to SUM Cells in Excel
When performing data operations in any context such as research, business, and academic projects etc. the sum operation is commonly used. Excel stores a number in an individual cell, and while performing the sum operation across the cells, the entire range(s) has to be taken into consideration. Excel provides various ways to sum the numeric contents in cells.
Syntax:
The syntax of SUM function is SUM(number1, number2… number n).
Examples of SUM Cells in Excel
Let’s go through the examples as described in the following section.
Example #1
We have Sales data of various products for a particular time period. The products have been represented by their product IDs. The dataset is as shown in the following screenshot.
Now, we intend to obtain total sales. This will, essentially, be sales obtained after adding sales for all the products. So, in the bottom, we will have “Total”. Using the SUM function we will obtain total sales. The SUM function will be implemented as shown below. Note, here we selected all those cells that contain sales figures for respective products. In SUM function, we can pass individual numbers separated by commas or pass range(s). Once done, complete the bracket and press Enter Key.
If we pass the argument (range of cells containing numeric values) properly into SUM function, then we get the correct result. The following screenshot shows the cells summed up using the SUM function. In the cell we got the total sales as a number and in the formula bar, we can see the formula that sums up the numeric contents in the mentioned range.
Example #2
Here, we have marks for five different subjects for a certain number of students. Now, our task is to obtain total marks for each student. This we will do by adding the marks for five marks using SUM function. Once implemented in the first cell, the function then will have to be copied down across all the requisite cells. The following screenshot shows the marks of data for students. In the Total columns, we want total of five subjects for each student.
We implemented the SUM function to sum cells across the range containing marks of five subjects as shown in the following screenshot. Observe how the function has been implemented. Once the requisite range is passed as an argument, close the function and press Enter Key. If implemented correctly, we shall obtain the right results.
As can be seen in the following screenshot, we obtained the total marks for each of the students using SUM function, following the above procedure properly. Note, in order to verify that right correct totals have been obtained just select the range which we had used for addition and see the sum in status bar. If both the figures match then we have successfully managed to sum cells to obtain total marks for each student.
In this example, summing marks for each subject across different students is not meaningful, as a total of marks for all students is never desired.
Example #3
In the first example we discussed, we summed cells across rows, and in the second column, we summer cell across columns. The summing of cells is governed by the context, and we have seen it with examples. In this example, we will require to sum across rows as well as columns, because both sums stand meaningful.
Let’s first understand the data. We have product sales data for five different years from 2014 to 2018. The sales figures for different products for these years are given as can be seen in the below screenshot.
Our task is to obtain total sales figures for each of the products for all the years, and total sales figures for all the products for each of the years. The former sum task essentially means summing across columns while the later sum task essentially means summing across the rows. First, we will perform sum of cells across columns i.e. total sales for each product across different years. We can obtain the sum by implementing the SUM function, as shown in the below screenshot. Observe how the function has been implemented and what range it takes into consideration. Once done, close the function and press Enter Key.
We obtained the total across all the years in the data, and the total figures can be seen as shown in the below screenshot.
We are just half done, and now we want to obtain total sales for each of the years. For this we implemented the function as can be seen in the below screenshot. Here, one fact must be noted carefully. As we can see the Year label is also a number, so while performing sum operation for summing across cells in the range, ensure that cell with year value is not selected or else we shall end-up obtaining incorrect results. Such scenarios are prone to this common blunder when obtaining sums through shortcuts.
We implemented SUM function as described in the preceding section and successfully obtained the results as can be seen in the following screenshot.
Once all the totals have been obtained, we should check if the results are correct. One of the ways to do this is to select the range that we intend to sum. Excel gives Average, Count, and Sum details for the selected range in the status bar. Validate the requisite figures by cross-checking. As can be seen in the following screenshot, the sum of numbers in the selected cells matches the sum figure in the status bar.
Things to Remember
- In Excel, we can sum across cells using SUM function, however, based on the context, it must be decided if summing across rows is feasible or summing across columns
- Sometimes, SUM may return error. These errors must be handled appropriately using suitable functionalities provided by Excel.
Recommended Articles
This is a guide to SUM Cells in Excel. Here we discuss How to SUM Cells in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
