Totalling columns in Excel 2007

  Channel 18:29 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 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 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 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 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 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 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 03 Jun 12

Problem solved?


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

Honor 5X review: Hands-on with the new budget phone from Hauwei's young brand

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

You can now publish data visualisations to the web from Power BI

Complete guide to Apple's campus 2, everything we know about the new spaceship HQ, plus: new drone…