5 ways to sum a column in Excel

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.
See the sum of the selected cells in the Excel Status bar

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.

  1. To avoid any additional actions like range selection, click on the first empty cell below the column you need to sum.
    Click on the first empty cell after the numbers you want to sum in Excel
  2. Navigate to the Home tab -> Editing group and click on the AutoSum button.
    Click on the AutoSum button
  3. You will see Excel automatically add the =SUM function and pick the range with your numbers.
    Select the range with the numbers you want to total and press Enter
  4. Just press Enter on your keyboard to see the column totaled in Excel.
    See the sum appear in your table

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.

  1. Click on the cell in your table where you want to see the total of the selected cells.
    Click on the first empty cell after the numbers you want to sum in Excel
  2. Enter =sum( to this selected cell.
    Enter the SUM function to the selected cell
  3. 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.
    Select the range with the numbers you want to total and press Enter

    That's it! You will see the column summed. The total will appear in the correct cell.
    See the sum appear in your table

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:

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.

  1. First, filter your table. Click on any cell within your data, go to the Data tab and click on the Filter icon.
    Click on the Filter icon
  2. You will see arrows appear in the column headers. Click on the arrow next to the correct header to narrow down the data.
    Click on this arrow to filter your data
  3. Uncheck Select All and tick off only the value(s) to filter by. Click OK to see the results.
    Tick off only the value or values to filter by
  4. Select the range with the numbers to add up and click AutoSum under the Home tab.
    Click on the AutoSum buttonVoila! Only the filtered cells in the column are summed up.

    Only the filtered values 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.

  1. Press Ctrl + T on yourkeyboardto format the range of cells as Excel Table.
  2. You will see the new Design tab appear. Navigate to this tab and tick the checkbox Total Row.
    Tick the checkbox Total Row
  3. 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.
    Pick the Sum option from the listUsing 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!

Table of contents

98 comments

  1. I have an excel sheet with data in rows, with the periodic billing order number in column A, customer ID in column B, and the approved amount in Column C. There might be only 5 rows of data for a particular customer ID or there might be twelve. I need to create a list which includes the total approved order amounts from column C for each customer ID in column B.

  2. How do I add numbers in a column to get a total? I’ve tried highlighting the entire column, selecting auto sum and all I get is a formula =SUM(). In previous versions all I had to do was highlight the column I wanted to add select auto sum and then my total appeared in the empty cell below.

  3. how about sum total formula from formula numbers how can i total, it cant be read if i used sum of..

    thanks for the answer.

  4. is there a way to assign a number value to a letter and do an auto sum from that?

  5. Hi Ryan,

    Thank you so much for a very helpful post. I am creating a timesheet template but the total for the hours worked is incorrect. On the particular cell, I used the formula: =SUM(G5:G11), and it should give the total number of hours worked in a week. Not sure what I am not doing right, the data entries are in the form: hhmmss.

  6. Is there a way to sum a column where every cell is the sum of 2 different cells? Every time I try it adds the formulas together and I end up with my original number.
    Ie
    C3 =b2-b3
    C4 =b3-b4
    C5 =b4-b5
    If c6 is my total how do I get c3+c4+c5?
    Please forgive me I am extremely new to excel

  7. THIS WAS VERY USEFUL, THANKS

  8. please i need someone to help. how can i add this numbers in a single cell 2,3,4,5,6 to give me 20 and "5-8" in a single cell to give me 26

  9. hey,
    i want to apply autosum on multiple rows at a time, is there a way to do that ? on my huje spreadsheet i have to keep on doing it individually for every single column.

  10. Hello, how will you rename a default sheet

  11. I have a table where I have to add weekly team performance data throughout the year, adding a new column each week for the new data. I have to show two sets of data; dataset one is overall totals and the overall average and dataset two is totals for the last four weeks and the average for the last four weeks. Are there formula I can use to automatically accommodate the additional columns,. at the moment I have to go in and manually adjust the formula each week.

    • Hello!
      To calculate the sum by row in the last 4 columns use the formula

      =SUM(INDIRECT(ADDRESS(ROW(),COLUMN()-4,1,1)&":"&ADDRESS(ROW(),COLUMN()-1,1,1)))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  12. When I'm using the sigma notationnto sum up I'm just getting 0 after i press enter on my computer.

  13. Hi
    My microsoft excel document was set up for me by a business start up company to add profits from row E and subtract expenses from row I to automatically give a running total in row J. I've used it without problems for many years, and today it's gone over £100,000 in row J, but instead of showing this amount, it's gone ######## Do you know how I can get it to show the figure? It seems the threshold of what can be displayed has been limited to below this, as if I put in an expense that puts it below £100K it shows the amount again... I'd really appreciate any help, many thanks! PS I could alternatively copy and paste everything into a new document to keep the years separate, but it's useful having it all in one place...

      • Hi Alexander,
        Many thanks for taking the time to reply with the simple solution of increasing the width of the column, it's much appreciated and it worked. Thanks again!

      • Hi,
        Changing the width of my columns didn't work for me. I still only have #### for my totals. What else can it be.

  14. I can't get any of your solutions to work. Was working happily with Excel 2010 until I bought the latest version and now I am really in the sh..house.
    It begins with the fact that a value of 25.10 is automatically converted to October 25 (except if I put a space in front of the number) and the Autosum button asks for the manual input of the range and then enters 0,0 (not even 0.0) as the result.
    Is there a way to go back to a previous version or simply click on the Σ as in the good old days?
    Best regards

  15. Hi. My son has a new business repairing Hydraulic hoses,he has set up a spreadsheet on his laptop with over 3,000 prices in column A.He needs to put a price rise of 6% on each price.How can he do this automatically instead of 1 by1 taking up hours of time and what formula can he use.Thankyou

  16. I have a spreadsheet used to record expenses that has x50 rows today but will be added to on a daily basis. I want to be able to total the expense amount column so that when I add new rows, the "totals" formula at the bottom remains correct (ie I don't have to change the range every time I add new rows). I thought I could enter eg =SUM(B:B) and it would always total column B but it returns 0 every time! It works in other sheets but not this one! What am I doing wrong please? Thanks

    • Update. I've formatted the column in question as "numbers". However, now when I enter the formula =SUM(B:B) it tells me that it's a circular reference!!!

  17. In column E I’m adding And I have a few minuses how would I do that and still get a total accurately with the minuses and it

    • Hello Cornie!
      If I understand your problem correctly, you want to summarize only positive values. To do this, use the SUMIF function. Read more on our blog here.

  18. i had alphabet E and N in each row and i wan to sum how many E and how many N. Kindly help.

  19. Here is my situation, any help would be much appreciated!
    I have 2 columns. Days of the Week and #of Steps. How would I write a formula that gives me the total sum of the #of steps for a particular day of the week?
    So if I want the total number of steps for Monday only, how would I do this without selecting each individual cell?
    Thanks in advance!

  20. Hey guys!
    This thread looks awesome. I am pretty sure I will get my answer here -
    I have a column, say D. There are negative as well as positive numbers in this column. I want the total of all negative numbers and put it in cell E4. How can I do that? Please suggest.

    Rahul.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)