How To Create A Sum On Excel

81 comments to "5 ways to sum a column in Excel"

Mike says:

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
- Hi!
  You write about the sum in a cell, but the formulas subtract. You can also read about the Sum formula in this article.
DIDI says:

THIS WAS VERY USEFUL, THANKS
didi says:

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
aleena says:

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.
- Hello!
  Please check out this article to learn how to sum selected cells vertically and horizontally.
  I hope it'll be helpful.
Kwasi Etor says:

Hello, how will you rename a default sheet
Ian Swift says:

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.
Tangeni says:

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

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...
- Hello Anna!
  Try increasing the width of the column with this number. I think this should help.
  - Anna Page says:
    
    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!
johnm says:

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
Denise says:

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
- Hello Denise!
  I recommend using Excel Paste Special.
  Read more in this guide.
Clare says:

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
- Clare says:
  
  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!!!
- Hello Clare!
  I think your formula = SUM (B: B) is written in one of the cells in column B. Thus, it refers to itself, which is unacceptable. Did you ignore the warning that appeared when you wrote this formula? Write it in another column.
  I recommend using the Excel Table to calculate the totals.
Corine jordan says:

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.
christine says:

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

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!
Rahul Khotkar says:

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.
Ed says:

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.
Nusrat Ullah says:

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 ....
Lizette says:

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
- Lizette says:
  
  A1 is my original amount and B1 is my final total which is higher or lower everytime
Ben says:

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
Mike says:

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
AT says:

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
Joleen says:

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
surya singh says:

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
sampath says:

can we get result(sum) in column b, where as in column A is given as 4+1+1 is written.
- Doug says:
  
  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.
govinda rao says:

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/-
- Doug says:
  
  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.
helen says:

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?
klitjon says:

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
Manuel says:

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!
living muhiirwe says:

The programme is good. How can i make a print out on an excel sheet?
camille says:

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?
- Pradeep says:
  
  =A1+10
  Copy and paste the formula in all the column.
Vijay Kumar says:

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
ji says:

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.
Karen says:

I am trying to add a total of names in a spread sheet. How do I go about doing that?
lulubelle says:

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.
Binu says:

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.
- Pradeep says:
  
  =SUM(A1:B2)+0.1*(SUM(FA1:B2))
kapil says:

how to add the 11-60 in excel in single field
Ron says:

I want to get a total of the last number entered in column L and the last number entered in column J automatically. That sum needs to be entered in column L as soon as I enter a number in column j.

Example: column L last number entered on line 380 + last number entered in column J line 381. sum to be automatic as soon as column J is entered. Want this to continue all the way until I finish all columns at end of work sheet.
What is the formula (or macro) I need?
Miguel Gareta García says:

Hello!

I have a doubt, although is not exactly about what is being discussed here... on summations in excel.
I want to add the values of columns and rows,

A B C
A X 1 3
B 2 X 8
C 4 2 X

To obtain: (A+B and B+A in their respective cells, etc)

A B C
A X 3 7
B 3 X 10
C 7 10 X

I have been checking how but haven't found yet. The problem is that I have huge matrices, that contain thousands of values, so I can't really do by hand one by one, plus I can make errors continously due to mistaking lines...It would take me weeks...

Could anyone tell me how to get this in an automatic way?

Thank you very much!

Best reagards,

miguel
Bob Dozier says:

I think this should be easy, but I can't seem to find a solution. If I have a cell that contains "=SUM(C9:C14)", what's the best way to automatically color fill all of the cells in that range? Similar to Crtl-[ but automatic. Thank you.
Yasemin says:

Could somebody help me in finding a formula that suits my needs? Through using conditional formatting, I used the highlight cell rule if it has text that contains key words. I want a formula to highlight the row in the same colour as the key word column. E.g. if cells in column c contain the word Apple, highlight the cells green. If cells C8 and C12 contain that word, I then want those two rows (8 and 12) to highlight green. I apologise if the answer's really obvious, I'm just really stuck!
- Hi Yasemin,
  
  Please try to do the following:
  1. Select cells with your data.
  3. Click Conditional Formatting -> New Rule.
  4. Select the "Use a formula to determine which cells to format".
  5. In the Formula field type the following formula:
  =NOT(ISERROR(SEARCH("Apple", $C1)))
  6. Click the Format button to set the format you need.
  7. Click OK.
rupesh Bhaje says:

hiii

sir/madam

i have to sum

1200 cft (i have to mention amount & Measurment both in each sales )
1300 cft
1400 cft
total---------?

please inform me
benito says:

kako u excelu dobijene vrednosti zaokruziti na 5 ili 9
npr. 27,2 =25 ili 27,6 =29
- Hi benito,
  
  You should use the following formula:
  =IF(MOD(A1, 5) >= 2.5, QUOTIENT(A1, 5) * 5 + 4, QUOTIENT(A1, 5) * 5)
Dragan says:

kako u excelu dobijene vrednosti zaokruziti na 5 ili 9
npr. 27,2 =25 ili 27,6 =29
Sugeetha says:

if i want get a sum of cells data in a column (if it is in time format like 7:30, 1:45 what is the method please ?

if i do it normal way it calculates up to 24:00 and total is a wrong figure ?
pavithra says:

how to do outstanding amount. example: 1+1+1 total = 3 outstanding 0

pLease advice
- Hi pavithra,
  
  To be able to assist you better please describe your task in more detail.
Michele says:

Is there a way to total the sums of only the highlighted cells in a spreadsheet? I accrue my expenses for each of my jobs and then highlight when the expense is received so it would be nice to be able to determine what expenses have been received already.
- Hi Michele,
  
  Please show us how your data looks like.
hemanth says:

i want to count total 10 cell in single short i am using sum formula, my problem is in between 2 cell have a formula that 2 cell values not count in total how to count 10 cell
naponica skannal says:

Good Afternoon, I wanted to know how do I capture all the total in line C.To add all totals together. I know I click on each total with the plus sign. It's just not working can you tell me what other keys I need to hold. Thanks
Alberta says:

Please help my on this formula
I want every cell in the row more than 60 to give me the difference otherwise if it is 60 than give me a 0
My formula looks like:
=IF('TOTAL WEEK 1'!$D$4:$R$4>60;'TOTAL WEEK 1'!$D$4:$R$4-60;0)
But if give me only a result if it is more in the first cell not in the following cells .
Thank you in advance
arman says:

بسم الله الرّحمن الرّحیم
hello
thank you very much.
It,s very good....
francis says:

How can you get the AutoSum of all fill column blocks.
Rawa says:

Hi, Is there any way to change for example =4+4+5+6 to column like
4
4
5
6

Regards
- Hi Rawa,
  
  First you need to use the Excel Text to Columns option to get the numbers in separate cells. Then use Paste Special -> Transpose.
Raghu Setty says:

when I insert data into columns and press auto sum, i get the total. But when I delete the data from one of the column, the total still remains the same. It doesn't change. How do i rectify this problem?
Ryan says:

Amanda,

The semi-manual approach to assist manual deletion would be to use Auto Filter or Sort to display sorted lists sorted by column C, then column B (where C is the same) then column A (where the previous two are the same), then column D (where all are the same). This way you get a block of rows with Jane, Smith, Excel Training Basics, Date and can keep the latest date and delete the rest.

The automated way would be to insert a new Worksheet to show the latest training only, leaving previous training on your existing Worksheet, say "Sheet1". You could then write a Visual Basic macro to copy rows from "Sheet1" into your new sheet, sort them and find the most recent dates of each instance where A, B, and C match up. Recording a macro where you manually invoke the appropriate Copy and Sort will help you see which functions to call to automate it, but it's hardly a beginner's task.
- Thank you for your help, Ryan!
- meshack says:
  
  Congratulations to you, am former. computer study I enjoyed the program of ISU
Amanda says:

Hi, Svetlana,

I'm a bit stuck on finding a formula that will suit my needs. My spreadsheet has column A: first name; column B: last name; column C: type of training; column D: date of training.

I need a formula that will look at column A, B, and C and if they match any other row in column A, B, and C I need it to select the most recent date.

this will remove all duplicate training and keep only their most current training record.

Any suggestions? i'm stumped on this one.