How to Sum Columns in Excel (7 Methods)

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


7 Methods to Sum Columns in 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. Get the Total Sum of a Column 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. Apply the AutoSum Option to Sum Columns in Excel

Excel’s AutoSum function 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. Calculate Total by Entering the Sum Function Manually in Excel Column

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. Transform Your 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. Add Up a Column in Excel Based on Criteria 

Sometimes we need to find out the total of a column based on 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. Calculate Subtotal for Filtered Cells in a Column in Excel

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. Find Out the 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.


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.


Related Articles

 Use SUM Function in Excel (With 6 Easy Examples)

SUMIF Function across Multiple Columns in Excel (4 Methods)

Use SUMPRODUCT Function in Excel (4 Examples)

SUMIF Function across Multiple Columns in Excel (4 Methods)

 Use SUMIF Function in Excel (With 5 Easy Examples)

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo