This article shows how to sum columns in Excel using 7 easy but effective ways. Applying these methods is going to speed up data analysis and help you to explore your dataset more confidently in Microsoft Excel.
To show 7 methods to get the total of a column in Excel, we’ll use the following dataset. It represents an order summary for 6 products for an online shop. The whole year is divided into parts like Jan-Apr, May-Aug, and Sep-Dec to show details of the order’s pattern throughout the year.
1. Getting Sum of a Column in Excel with One Click
This is a quick solution to get the total of a column in Excel without storing the data in a cell. Sometimes it needs to get the total of the whole column or only some selected cells of a column.
- Select a Whole Column: We can select all the cells of a column by clicking the letter of the column. The Excel status bar will show the sum along with the cells count. Here, we selected the E column to get the sum in the status bar.
- Selected Cells of a Column: It only requires selecting cells having our desired data and the status bar will show the value of the summed data.
Here we selected 3 cells (E8:E11) to get the total for pencils order during Sep-Dec.
2. Using AutoSum Command to Sum Columns in Excel
Excel’s AutoSum feature is a frequently used way to get the column totaled. Follow the simple steps:
- We’ll first select the empty cell immediately below the cells that we need to add up. In the Home tab, click the AutoSum option from the Editing group.
- Excel will add the SUM function and range of cells in the selected cell.
- Now, we need to press Enter key to get and store the column total in cell C11.
3. Calculating Total by Entering SUM Function Manually
We can put the SUM Function to calculate the sum. A column can be partially or fully selected to perform the calculation. It is one of the most widely used methods in Excel. Let’s see that with an example:
- At first, select the cell where we want to see the summed value and write the SUM function.
- After that, select the cell range. As we want to know the sum of orders for Jan-Apr, we selected C5:C10.
- Finally, hit the Enter key to get the result.
4. Transforming Data into Excel Table to Sum Columns
This method works fine for a dataset having a lot of columns. Transforming the dataset into an Excel table gives an easy way to calculate the total for the columns. Let’s do an example:
- In the 1st step, select the dataset. Then, from the Insert tab choose the Table button to click.
- Click OK on the Create Table window. This will turn the dataset into an Excel table.
- Go to the Design tab and check the Total Row box.
- As an output, we can see the sum of the orders for the Sep-Dec column in the E11 cell.
- To get the totals for other columns (Jan-Apr and May-Aug), get the Autofill Handler and drag it to the left.
- Now, we have all three totals as a final output.
5. Adding Up a Column in Excel Based on Criteria
Sometimes, we need to find out the total of a column based on the required criteria. To achieve this, we have to use the SUMIF function. In this example, we took the sum for the three-time duration but only for orders greater than 100. Let’s explore further:
- At first, select the cell that will hold the summed value. Then, write the formula into that.
And hit Enter.
- The above formula results in 560 which is the sum of 125, 325, and 110.
- By using the Autofill Handler we can also calculate the totaled columns for the other two-time durations based on the criteria.
6. Calculating Subtotal for Filtered Cells in Excel Column
To get the total for only the visible data, we can use the filter feature of Excel. This will allow us to show the filtered result required for data analysis of a big set of data. Here is an example:
- First, we’ll select the columns that are needed to be filtered. Then, from the Data tab select the Filter option.
- This will show a filter option with an arrow like this.
- Clicking this arrow will open up a window from where we’ll check the option for pens only (Black Pen, Red Pen, and Blue Pen).
- Now, our dataset is filtered only for pen items.
- In this step, we used AutoSum to calculate totals for the columns.
7. Finding Out Running Total of an Excel Column
This method will show how to calculate the running total of a column in Excel using the SUM function. Let’s dive in:
- In the first cell of the Running Column of Jan-Apr column, put the following formula
- Get the Autofill Handler and drag it down to the bottom of the column.
- Output is the running total for the Jan-Apr column.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Finally, we reach the end of the article and got familiar with different ways to calculate a sum of a column in Excel. Any questions or suggestions, don’t forget to put them in the comment box below.