All the Easy Ways to Add up (Sum) a column in Excel

Adding up all the cells in a column is one of the most used functions in Excel. In this article all the ways to add up a column in Excel will be discussed. So, after going through the article, you will be able to apply multiple methods for adding up columns in excel in all conditions.

Consider the following dataset. Here No of sales and dollar sales of different salesmen are given. We want to know the total number of sales made by all the salesmen. For that we have to sum up column C.

Dataset for sum a column

Download Excel Workbook

1. Get the Sum of a Column in the Status Bar

It is the simplest way to get the sum of a column. All you need to do is select the column you want to add up. You will get the sum in the bottom right corner of your excel window. One of the disadvantages of this method is you won’t be able to copy the value.

Sum in status bar

Read More: How to Sum Multiple Rows and Columns in Excel

2. Get the Sum of a Column Using AutoSum

Select the empty cell in the end of your column > Go to Formulas > Select AutoSum>Sum> Press ENTER.

Autosum 

You will get the add up of the column in your selected cell.

Autosum

Keyboard Shortcut for AutoSum

You can also get the result by using a keyboard shortcut. select the empty cell in the end of your column> press ALT and = key > press ENTER

The disadvantage of this method is, if there is any empty cell in the column, you will only get the sum of the cells after the last empty cell in that column. So if your dataset has at least one empty cell in the column you want to sum up, do not use this method.

Read More: Excel Sum Last 5 Values in Row (Formula + VBA Code)

3. Get the Sum of a Column Using the Sum function

Using the SUM function to add up a column in Excel is the most convenient way. You can apply the sum function in all types of datasets. You can use the SUM function in multiple ways.

i. Add Up manually using the SUM function

Type the formula in the first empty cell in the end of the column

=SUM (Select the cell one by one, you want to sum up)

SUM 

After pressing enter you will get the sum up in that cell.

If your dataset is long, do not use this method. It will consume a lot of time. For a long dataset the following methods are recommended.

ii. Sum of Whole column

Type the formula in the first empty cell in the end of the column

=SUM (Select all the cell in that column, you want to sum up)

You can select all the cell in that column by dragging your cursor or by selecting CTRL+SHIFT+DOWN ARROW key

SUM

After pressing enter you will get the sum up in that cell

iii. Sum of selected cells in the column

If you want to sum up some selected cells in that column instead of the total column, you have to manually select the cells you want to sum up.

Type the formula in the first empty cell in the end of the column

=SUM (Select the cells, you want to sum up)

You can select the cells by pressing ctrl key and clicking on the cells by your cursor

SUM

After pressing enter you will get the sum up of the selected cells.

Read More: How to Add Multiple Cells in Excel (6 Methods)

iv. Sum by Using Named Range

If your column has a given Name, you can also use this name to sum up the column. This method is particularly useful when you have data randomly in different cells of that column.

To apply this method at first you have to give a name to the column. For that select the column> type the name in the name box> press ENTER.

After that, to get the sum of the column, you have to select a cell in any other column and type the formula,

=SUM (Your Given Name)

Read More: Sum Cells in Excel: Continuous, Random, With Criteria, etc.

4. Get the Sum using Table

You can also add up a column by forming a table. To form a table, Go to Insert ribbon > click table. A box will appear. Select all your data in table range, check my table has header box if your data has header row and press OK.

Create Tab;e

After forming the table you can easily get the sum up. Go to Table design> Check the Total Row box.

It will show the sum up in the next column of your data.

Table


Similar Readings


5. Sum of a Column using AGGREGATE function

To get the sum of a column using the AGGREGATE function you have to type the formula in an empty cell,

= AGGREGATE (function_num, options, array)

AGGREGATE

Here, for making sum, function_num= 4

you can use different number to option for different criteria. For summing all the cells options= 4

array= Range of your data, for our dataset which is C6:C14

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)


6. Sum of a Column using SUBTOTAL function

To get the sum of a column using the SUBTOTAL function you have to type the formula in an empty cell,

= SUBTOTAL (function_num, ref1)

SUBTOTAL function

Here, for making sum, function_num= 9

ref1 = Range of your column, for our dataset which is C6:C14

SUBTOTAL

7. Get the Sum of Column based on a Criteria

When a criteria is given, to make the same you have to use SUMIF or SUMIFS function

i. Numeric Criteria

Suppose we need to sum up sales over $10000. For doing this, you have to type the formula

=SUMIF (range, criteria, [sum_range])

SUMIF 

Here, range= cell ranges where criteria will be checked = C6:C14

criteria= comparing equation, for our dataset “>10000”

[sum_range] = cell range which contains the value.

SUMIF

After pressing enter, summation based on given criteria will be shown.

ii. Text Criteria

Consider the following dataset, where one salesman has multiple sales entries. We want to know total amount of sales made by Suman

For doing this, you have to type the formula

=SUMIF (range, criteria, [sum_range])

Here, range= cell ranges where criteria will be checked = A5:A15

criteria= comparing text, for our dataset “Suman”

[sum_range] = cell range which contains the value= C5:C15

SUMIF

After pressing enter, sum based on given criteria will be shown.

Read More: Sum If a Cell Contains Text in Excel (6 Suitable Formulas)

8. Get the Sum of multiple column

Consider the following dataset where sales of 2 weeks of different salesmen are given. We want to know the total sales of these two weeks. For finding the value we have to add column B and C

For doing that we have to select an empty cell and then type “= SUM ()” function and select all the values of column B and C. After pressing OK we will get the result.

SUM

Read More: How to Sum Multiple Rows in Excel (4 Quick Ways)

9. Get the Sum of a Column When you already have the sum of another similar column

Suppose we know the total sales of week 1 and now we need to know the total sales of week 2. It is a very easy process; all you need to do is putting your cursor in the bottom right corner of the cell (A plus sign will be shown) and dragging the cell of Sum of week 1 to the cell of Sum of week 2

SUM

Conclusion

Summing up a column is not a very difficult task. After practicing on your own you will be able to sum up a column in any given situation. If you face any type of problem while adding up a column, feel free to ask in the comment box.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo