by This tutorial shows how to sum a column in Excel 2010 - 2016. Try out 5 different ways to total columns: find the sum of the selected cells on the Status bar, use AutoSum in Excel to sum all or only filtered cells, employ the SUM function or convert your range to Table for easy calculations.
If you store such data as price lists or expense sheets in Excel, you may need a quick way to sum up prices or amounts. Today I'll show you how to easily total columns in Excel. In this article, you'll find tips that work for summing up the entire column as well as hints allowing to sum only filtered cells in Excel.
Below you can see 5 different suggestions showing how to sum a column in Excel. You can do this with the help of the Excel SUM and AutoSum options, you can use Subtotal or turn your range of cells into Excel Table which will open new ways of processing your data.
How to sum a column in Excel with one click
There is one really fast option. Just click on the letter of the column with the numbers you want to sum and look at the Excel Status bar to see the total of the selected cells.
Being really quick, this method neither allows copying nor displays numeric digits.
How to total columns in Excel with AutoSum
If you want to sum up a column in Excel and keep the result in your table, you can employ the AutoSum function. It will automatically add up the numbers and will show the total in the cell you select.
- To avoid any additional actions like range selection, click on the first empty cell below the column you need to sum.
- Navigate to the Home tab -> Editing group and click on the AutoSum button.
- You will see Excel automatically add the =SUM function and pick the range with your numbers.
- Just press Enter on your keyboard to see the column totaled in Excel.
This method is fast and lets you automatically get and keep the summing result in your table.
Use the SUM function to total a column
You can also enter the SUM function manually. Why would you need this? To total only some of the cells in a column or to specify an address for a large range instead of selecting it manually.
- Click on the cell in your table where you want to see the total of the selected cells.
- Enter
=sum(to this selected cell.
- Now select the range with the numbers you want to total and press Enter on your keyboard.
Tip. You can enter the range address manually like
=sum(B1:B2000). It's helpful if you have large ranges for calculation.
That's it! You will see the column summed. The total will appear in the correct cell.
This option is really handy if you have a large column to sum in Excel and don't want to highlight the range. However, you still need to enter the function manually. In addition, please be prepared that the SUM function will work even with the values from hidden and filtered rows. If you want to sum visible cells only, read on and learn how.
Tips:
- Using the SUM function, you can also automatically total new values in a column as they are added and calculate the cumulative sum.
- To multiply one column by another, use the PRODUCT function or multiplication operator. For full details, please see How to multiply two or more columns in Excel.
Use Subtotal in Excel to sum only filtered cells
This feature is perfect for totaling only the visible cells. As a rule, these are filtered or hidden cells.
- First, filter your table. Click on any cell within your data, go to the Data tab and click on the Filter icon.
- You will see arrows appear in the column headers. Click on the arrow next to the correct header to narrow down the data.
- Uncheck Select All and tick off only the value(s) to filter by. Click OK to see the results.
- Select the range with the numbers to add up and click AutoSum under the Home tab.
Voila! Only the filtered cells in the column are summed up.
If you want to sum visible cells but don't need the total to be pasted to your table, you can select the range and see the sum of the selected cells on the Excel Status bar. Or you can go ahead and see one more option for summing only filtered cells.
Convert your data into Excel table to get total for your column
If you often need to sum columns, you can convert your spreadsheet to Excel Table. This will simplify totaling columns and rows as well as performing many other operations with your list.
- Press Ctrl + T on yourkeyboardto format the range of cells as Excel Table.
- You will see the new Design tab appear. Navigate to this tab and tick the checkbox Total Row.
- A new row will be added at the end of your table. To make sure you get the sum, select the number in the new row and click on the small down arrow next to it. Pick the Sum option from the list.
Using this option lets you easily display totals for each column. You can see sum as well as many other functions like Average, Min and Max.
This feature adds up only visible (filtered) cells. If you need to calculate all data, feel free to employ instructions from How to total columns in Excel with AutoSum and Enter the SUM function manually to total the column.
Whether you need to sum the entire column in Excel or total only visible cells, in this article I covered all possible solutions. Choose an option that will work for your table: check the sum on the Excel Status bar, use the SUM or SUBTOTAL function, check out the AutoSum functionality or format your data as Table.
If you have any questions or difficulties, don't hesitate to leave comments. Be happy and excel in Excel!
98 comments
None of these work for me. I am trying to total up a column that has numbers and letters in it. It is disk space sizes that have like 13 GB for example in one of them. I assume the GB part is causing this not to work, but I see no way to remove the GB piece from the column without going through each cell manually and removing it.
hi I have problem in excel hope u will help me to solve
I want to add A Row1 and A Row2 and answer should be in B Row 1 and so on I want to apply the same formula to whole column that I only have to enter the data in up comming cell answer should be in next row ....
How can I add and subtract two cells? For example I want A1 (add and subtract) B1= C1? Both A1 and B1 are dollar amounts
A1 is my original amount and B1 is my final total which is higher or lower everytime
Hi Lizette,
You can simply use the plus and minus signs. For example, =A1+B1 or =A1-B1
For more information, please see: How to do calculations in Excel
I want to do autosum in excel I have highlights all cell D and click the autosum it only counts no average and no sum pls help
Hi,
What formula can I use to sum a column in Excel where the range is extracted from values in another two cells.
In the following example, I want to sum column A between rows 3 and 5 ie (18 + 24 + 31) = 73
The required range is contained in cells B1 and B2
A B
1 12 3
2 16 5
3 18
4 24
5 31
6 39
Any help appreciated!
Thanks!
Mike
Which of the following formulas or functions are correct? The fact that they may work in Excel is not enough ... they have to be correct usages! You may refer to SIMbook or see the function definition in Excel. Remember what we discussed in class …
1) A1+B3+C14
2) =SUM(A1:C3)
3) =SUM(A1:A10) correct
4) =SUM(A1,B4,C4,D15) correct
5) =SUM(A1+B4+C4+D15)
6) =A1+B4+C4+D15
7) =SUM(D3*A10)
8) =SUM(A1-B3*10)
9) =A1-B3*10
Hi,
Something about Excel that bothers me. You sum a column, then it automatically takes you back up to the top of the page, rather than leaving you at the Sum. Can you change this?
Thanks,
Joleen
Hi All,
I have a data of around 150 employees in one excel sheet , one after the other
i need a formula or macro (Module) so that i can find subtotal of every employee (after every 5 or 6 row) and grand total of all employee after 150 employee
what am doing right now is manually adding formula (=SUM(K2:K5,K8:K12,K15:K21)
and i want subtotal of K2:K5 at K6 then K8:K12 at k13 and K15:K21 at K22 hope this clarify my queries to you guys and do reply on this thanks
can we get result(sum) in column b, where as in column A is given as 4+1+1 is written.
Sampath:
If the sum of 4+1+1 is in cell A1 then in the cell where you want to display this result enter =A1.
how to apply autosum for the below values, in excel sheet.
2,443/-
14,997/-
1,04,195/-
37,076/-
1,908/-
19,469/-
62,975/-
37,600/-
2,510/-
4,496/-
23,770/-
Govinda:
You've got to remove the /- characters from the cells before you can sum the values.
Where the data is in cells A1 to cells A12,
Select the cells, go to Find/Replace, then Replace "/-" with nothing, after this is complete enter SUM(a1:a12) in the cell where you want to display the sum. The "1,04,195" won't be included in the operation because it is not a number, it's text.
I would like to add say A6 to B6 but the next month add a new amount in A6 to B6 what will the formula be?
hi,
i have a big probelm, i want to spread the sum in different celle.
ex: the sum is 45, and this sum i want to spread in celle A4 A8 A12 ( to do this only selected the celle).
thanks
Good day, here my dilemma, I would like to add F8 & M8 only no ranges between the two, how is that possible?
Help Is welcome..
Thank you
Hello,
If I understand your task correctly, please try the following formula:
=SUM(F8,M8)
Hope this will help you!
The programme is good. How can i make a print out on an excel sheet?
How can i add +10 to all the amount that the column have. For example i had 120 and i want to add a value +10.. what formula can i use?
=A1+10
Copy and paste the formula in all the column.
i want to add different values in a single column like1,2,0,10 and column is total n in numbers . than how will we calculate it .
I am not able to sending the screenshot,if i will share the screenshot it will east to see what i want
this is stupid. people already know how to do this junk. how do you sum up the entire column that could be 2 rows today and 34904it0934i860938663 rows tomorrow. why bother putting up the same easy garbage that everyone else on here has. be original and do something useful.
I am trying to add a total of names in a spread sheet. How do I go about doing that?
I am doing a item retail price list I need to convert each individual line total from wholesale to retail for example $34.00 needs to actually read $136.00 so its the 1st price x 4 I'm new to using excel and don't really have time for researching the tutorials help pleas
Hi Lulubelle,
if the totals are in J column (starting from J1) and you need to multiply them by 4 and return the result in column H, then put the following formula in H1:
=J1*4
To apply the formula to the whole column, position the cursor to the lower right corner of the cell with the formula, wait until it turns into the plus sign, and then double-click the plus.
If you want to calculate everything in the same column, you will need to read this part of the article, since there are some important steps to follow.
Hello all,
I need a formula where I can add column A and column B and then add 10% of the total.
For example: 10+10 and 10% of the total (10+10) in column C.
=SUM(A1:B2)+0.1*(SUM(FA1:B2))