Cells not adding up correctly

[Geek Girl 007]

I have created this timesheet so that only certain cells will show when they are full etc.
However to total 'excess hours' is not adding up correctly 18:2, is what I make the total as in week 4 there was no excess.....
I don't know why I can't get this to work
I tried to post a XL2BB version into this post but even that wouldn't work for me

 

[Geek Girl 007]

The 3 weeks that are showing are 5:40 - 9:30 - 3:21

 

[Geek Girl 007]

I've had another look at it and realize that if one of the weeks has no excess the sum won't work, why is this?
Can it be fixed?

 

[Fluff]

Try it like

=SUM(E20,E31,E42,E53)

 

[Geek Girl 007]

Try it like

=SUM(E20,E31,E42,E53)

Unfortunately still no, I think it's the blank cells that are causing the issue, I tried a running total, it was all fine until I got to the blank cell.
I rechecked it with all 4 cells having time and it worked??

 

[Fluff]

What is the formula in those cells?

 

[Geek Girl 007]

=I42+J42+L42-M7

On each line it is the same number but different letters.

 

[Fluff]

That formula will not result in a blank cell, but it with the data shown it will return a -ve value, which might be why your sum is not returning what you think is the correct value

 

[Geek Girl 007]

I equaled the cell into another cell fro just the value and then pasted it a a value i got this -0.200694444444445
it looked like this:

 

[Fluff]

As the cell is formatted as time you will get the #### as xl does not display negative times, but it will still alter the value returned by SUM.