 # Summing Adding Many Time Cells

• I have a spreadsheet with many rows of time totals as the following example

Cell 1 - 08:00 AM Cell 2 - 1:00 PM Cell 3 - 5:00

Cell 3 is just the total, and I have no problem with this aspect... However I have two columns of the above format side by side... and the only way I can sum Cell 3 on both columns is something like the following:

=sum(c1+c2+c3+f1+f2+f3...etcetcetc...

This can end up in a large formula, and I was just wondering if there was a more efficent way? I tried the following but get a #value! error.

=SUM(C1:C6)+(G1:G6)

• Re: Summing Adding Many Time Cells

Try:

=SUM(C1:C6,G1:G6)

• Re: Summing Adding Many Time Cells

The #value! error is due to faulty Formula. There are two simple ways to fix it:

=Sum(C1:C6,G1:G6)
or
=Sum(C1:C6) + Sum(G1:G6) (don't forget the second sum)

• Re: Summing Adding Many Time Cells

That code seems to work to some degree... but on the spreadsheet I'm using at work, with the following code, it gives me a result of 44 something hours and when I calculated it manually, it was more like 41 something hours. It always seems to add a little on to the result.

The code I've used is the one like this: =Sum(C1:C6,G1:G6)

Any idea what could be causing it?

• Re: Summing Adding Many Time Cells

I think you will win a large prize if it's an Excel error. Can you post your workbook so we can tell you what you've won?

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Summing Adding Many Time Cells

That could get me into trouble with my job, so no, hehe!

I tried making a basic sample at home but the code seemed to work fine and return the correct amount... albeit the spreadsheet at work has more data and more differentials in the inputs. But as far as I can tell, the sample reflects the same procedures and formats in the spreadsheet I use at work.

• Re: Summing Adding Many Time Cells

What formula are you using to find the time difference between A1 and B1, are both times always on the same day?

• Re: Summing Adding Many Time Cells

edit:

I have been messing around with my sample sheet, and instead of having all the totals as 5 hours: I changed it to the following... Hope this makes sense, but this is what I have on Excel:

One of the cells has this code.

=SUM(C1:C6,F1:F6)

This gives me a total of "36:25", but when I calculate it manually, I get "35.45"... So again, I keep getting a total slightly lower than the formula when I calculate it manually.

• Re: Summing Adding Many Time Cells

I get 36:25 by either method.

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Summing Adding Many Time Cells

You are right, I just calculated it the second time and got a match... I know what I did wrong, now I need to check the other spreadsheet at work tommorrow to make sure I'm not making the same mistake there

Doh

• Re: Summing Adding Many Time Cells

Quote

Doh

We've all been there ...

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]