# Adding times using SUM always gives 0

#### ColDrum

##### New Member
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??

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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 ?

Yes Aladin. It continues to give zero

Yes Aladin. It continues to give zero

Care to post the formula you have?

Nothing complicated, just
=SUM(M3:M5)

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

This also gives 0.

Any other ideas?

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

Last edited:
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 ?

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)

Replies
3
Views
113
Replies
5
Views
71
Replies
33
Views
472
Replies
1
Views
102
Replies
0
Views
199

1,196,152
Messages
6,013,742
Members
441,781
Latest member
Gian Carlos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back