Totalling columns in Excel 2007

  Channel 31 May 12
Locked

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 31 May 12

Channel

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

TonyV

  lotvic 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 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?

Regards,

Simsy

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

Problem solved?

Advertisement

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

How to watch Wimbledon 2015 live online: follow the tennis from your laptop or PC

We are being sold the ability to spend money we don't have. And we love it

HTC's head designer on what's exciting in designing for mobile right now

Apple Music preview: Why Spotify should be scared of Apple's impressive new music-streaming service

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message