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
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 !

Like this post
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

Like this post
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.

Like this post
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

Like this post
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.

Like this post
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.

Like this post
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)

Like this post
Woolwell

Likes # 0

Problem solved?

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

Microsoft Band UK release date and price rumours, features and specs: Microsoft smartwatch unveiled

IDG UK Sites

Why Sony's PS4 2.0 update is every gamer's dream (well, mine at least)

IDG UK Sites

Watch new Grolsch ad that combines stop-motion & CG for majestic results

IDG UK Sites

Apple rumours and predictions for 2015: What to expect from Apple in 2015