Prantick
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.
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.
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.
Â
You will get the add up of the column in your selected cell.
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: How to Sum a Column in Excel
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
Â
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
You can select all the cell in that column by dragging your cursor or by selecting CTRL+SHIFT+DOWN ARROW key
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
After pressing enter you will get the sum up of the selected cells
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,
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.
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.
5. Sum of a Column using AGGREGATE function
To get the sum of a column using AGGREGATE function you have to type the formula in an empty cell,
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
Similar Readings:
6. Sum of a Column using SUBTOTAL function
To get the sum of a column using SUBTOTAL function you have to type the formula in an empty cell,
Here, for making sum, function_num= 9
ref1 = Range of your column, for our dataset which is C6:C14
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
Â
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.
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
After pressing enter, sum based on given criteria will be shown
Read more: How to Sum Cells with Text and Numbers in Excel
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.
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
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.
