How to Sum Columns in Excel (7 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Sum Columns in Excel Dataset


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.

Sum Columns in Excel One Click

  • 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.

Sum Colums in Excel

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.

sum Columns in Excel using Autosum

  • Now, we need to press Enter key to get and store the column total in cell C11.

Sum of Columns in Excel


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.

Sum of Columns in Excel

  • 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.

Sum of Columns in Excel using Table

  • As an output, we can see the sum of the orders for the Sep-Dec column in the E11 cell.

Sum of Columns in Excel

  • 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.

Sum of Columns in Excel


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.
=SUMIF(C5:C10,">100",C5:C10)

And hit Enter.

Sum of Columns in Excel Using Criteria

  • The above formula results in 560 which is the sum of 125, 325, and 110.

Sum of Columns in Excel Using Criteria

  • 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.

Sum of FIiltered Columns in Excel

  • This will show a filter option with an arrow like this.

Sum of FIiltered Columns in Excel

  • 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).

Sum of FIiltered Columns in Excel

  • Now, our dataset is filtered only for pen items.

Sum of FIiltered Columns in Excel

  • 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
=SUM(C$5:C5)

  • Get the  Autofill Handler and drag it down to the bottom of the column.

Running Sum of Columns in Excel

  • 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.


Conclusion

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.


How to Sum Columns in Excel: Knowledge Hub


<< Go Back to How to Sum in Excel | How to Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo