Adding times using SUM always gives 0

[ColDrum]

I'm trying to add up a range of times using SUM but I always get the time of 0:00, 00:00, or 00:00:00 etc etc

When I add the cells using + it works perfectly but when using the SUM function it always gives 0.

I've tried searching online for over an hour now and tried lots of different solutions but none have worked.

Can anyone help??

 

[Aladin Akyurek]

I'm trying to add up a range of times using SUM but I always get the time of 0:00, 00:00, or 00:00:00 etc etc

When I add the cells using + it works perfectly but when using the SUM function it always gives 0.

I've tried searching online for over an hour now and tried lots of different solutions but none have worked.

Can anyone help??

Did you format the formula cell as: [h]:mm ?

 

[ColDrum]

Yes Aladin. It continues to give zero

 

[Aladin Akyurek]

Yes Aladin. It continues to give zero

Care to post the formula you have?

 

[ColDrum]

Nothing complicated, just
=SUM(M3:M5)

As I say, when I do =M3+M4+M5 it all displays correctly

 

[ps1]

how about
=subtotal(9,"your range")

 

[ColDrum]

how about
=subtotal(9,"your range")

This also gives 0.

Any other ideas?

 

[ColDrum]

The range of cells I am trying to add do contain formulae, pulling data from another worksheet.
If I type a value into the range of the cells I am trying to add, the formula works correctly and gives me the correct answer.

It's as if Excel does not see the original data as a time value.
I have tried formatting the cells of the original data to [h]:mm but still no luck

 

[Aladin Akyurek]

Nothing complicated, just
=SUM(M3:M5)

As I say, when I do =M3+M4+M5 it all displays correctly

They are probably text numbers...

What do we get with:

=SUMPRODUCT(M3:M5+0)

with the formula cell formatted as: [h]:mm ?

 

[ColDrum]

They are probably text numbers...

What do we get with:

=SUMPRODUCT(M3:M5+0)

with the formula cell formatted as: [h]:mm ?

Superb Aladin! Thank you so much!

Now, to combine SUMPRODUCT with my SUMIF...?

=SUMIF('Shrink Lookup'!L:L,'CHD Off Phone'!$B20 & "",'Shrink Lookup'!M:M)