Totalling columns in Excel 2007

  Channel 18:29 PM 31 May 12

I have a spreadsheet for household accounts.I wish to total columns of mixed positive and negative numbers(currency) at the month's end.Using the quick sum method gives incorrect totals.How can I do it-other than by hand with a calculator ? (The columns have 46 rows.)There must surely be a way.I am fairly new to Excel, so if there is a straightforward way, I'd prefer it.Excel help doesn't help !

  TonyV 19:23 PM 31 May 12


Surely you can use =SUM(A12:A32) in any column, where A12 is the start cell and A32 is the last cell.


  lotvic 19:56 PM 31 May 12

Yes, if you want the total to show in cell A47, just put cursor in cell A47 and type =SUM(A1:A46) and then press Enter.

  Simsy 20:31 PM 31 May 12

When you say the incorrect answers are given, can you be a little more specific?

How are you differentiating the positive and negative values?



  Channel 22:12 PM 31 May 12

I'm not differentiating values other than putting negative ones, in red,and such as -£25.13 If I add the total column, including both positive and negative numbers, the total doesn't tally with previous columns-eg column A total is £5795.56, column B:-£1435.18, column C:£4,580.88.But, deducting columnB from A the total should be £4360.38.They should agree.I have checked each individual figure in column C and they are correct.(Each is A-B)I hope this clarifies it, though it is complicated.I had read that Excel can't cope with mixed negative and positive numbers in a column.

  Woolwell 22:33 PM 31 May 12

It's your maths that is faulty. Column A positive 5795.56, column B negative 1435.18: A-B = 7230.74 (that is the difference between the 2). 4630.38 is the total (sum) of A and B ie A+B. You are not taking the minus sign correctly. I cannot work out how you got 4580.88.

  lotvic 00:50 AM 01 Jun 12

Check the formula for the total of column C for the cells that are counted. It should read =SUM(C1:C46)

I think you have missed out one cell whose value is -£220.50, (probably the top one C1)

Column Totals: eg,Total of column A in cell A47 formula =SUM(A1:A46) the colon means 'through to'

Total of column B in cell B47 formula =SUM(B1:B46)

Total of column C in cell C47, formula =SUM(C1:C46)

(Obviously you can put the totals formula in any cell you want, it doesn't have to be on row 47)

Row Totals: The formula in C1 for the total of A+B is =SUM(A1+B1) and in C2 is =SUM(A2+B2) etc. etc. or if you are using Autosum it will show =SUM(A1:B1)

you ADD the negative cell. eg, =SUM(A47+B47) or if you are using Autosum it will show =SUM(A47:B47)

  Woolwell 13:47 PM 03 Jun 12

Problem solved?


This thread is now locked and can not be replied to.

Best Black Friday deals 2015 live blog: Best UK Black Friday deals & best UK Black Friday tech…

1995-2015: How technology has changed the world in 20 years

HDR video: Everything you need to know

20 Mac Power User Tricks... That You Didn't Know