# How do I calculate the total difference between two columns of figures in Google spreadsheet/Excel?

Usually I would just calculate the difference of each row like this: =B1-A1

Then I would sum up the values of the column like this =SUM(C1:C10) and get my desired value.

I don't want the C-column. Is it possible to calculate it immediately?

For example I tried: =SUM((B1-A1):(B10-A10)) (which doesn't work)

Usually I can find the answers somewhere in the documentation, but my Math-English vocabulary is a little bit rusty and with German documentation I haven't found what I was looking for.

I know I could put 10 values manually inside the sum. But this doesn't scale gracefully, I want to be able to change only a few values in the formula instead of adding 1000.

-

The following should work for this case:

=SUM(B1:B10) - SUM(A1:A10)

-
Actually i tried it with date but ended up with a weird value. But when i think about it it makes sense (hit brick on my head). Changed it to hours and the value is correct now. Do you know, how to do it with date format? – proxylittle Mar 26 '13 at 15:40
How can I ensure this number is always positive? I want to calculate how far off from an estimate we were... either positive or negative. – ATSiem Sep 25 '13 at 20:42
@ATSiem: It's probably long past useful, but the ABS() function ought to help. – telmer6 May 14 '15 at 0:02

If you want to calculate each value in turn, evaluate it as a positive number, THEN work out what that adds to then your best bet is an array formula. These are entered not by pressing ENTER at the end, but CTRL+SHIFT+ENTER. When done, you will notice a pair of braces either side ( { } ). Note - you must use this method, you cannot type the braces in

e.g. =sum(abs(b1:b10 - a1:10)) CTRL+SHIFT+ENTER

Clearly, you can replace sum with average if that better suits your needs.

-