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

Contact Forum Editor

Send an email to our Forum Editor:

PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

# Tech Helproom

It's free to register, to post a question or to start / join a discussion

# Totalling columns in Excel 2007

Channel

Likes # 0

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

Likes # 0

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

Likes # 0

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

Likes # 0

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

Likes # 0

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

Likes # 0

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

Likes # 0

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

Likes # 0

Problem solved?