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??

 

[jamtay317]

=sumproduct(--('shink Lookup'!L:L='CHD Off Phone'!$B20 & "")*(M3:M5+0))

 

[ColDrum]

=sumproduct(--('shink Lookup'!L:L='CHD Off Phone'!$B20 & "")*(M3:M5+0))

Thanks but I'm using SUMIF'ing the whole of column M, not just M3:M5.

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

 

[jamtay317]

=sumproduct(--('shink Lookup'!L:L='CHD Off Phone'!$B20 & "")*(M:M+0))

 

[jamtay317]

=sumproduct(--('shink Lookup'!L3:L50000='CHD Off Phone'!$B20 & "")*(M3:M50000+0))

 

[ColDrum]

=sumproduct(--('shink Lookup'!L:L='CHD Off Phone'!$B20 & "")*(M:M+0))

That doesn't work either - back to giving me 0.
I expanded what you gave me to say:

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

 

[ColDrum]

I need to get the SUMIF working alongside the SUMPRODUCT.
jamTAY, what you've given me doesn't work - does it not need the 2nd comma to get to the sumrange in the SUMIF function?

 

[Aladin Akyurek]

Superb Aladin! Thank you so much!

That's great.

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

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

Would you elaborate on combining the SumProduct bit with the SumIf bit you have?

 

[jamtay317]

i know, thats why I change it, and changed the range that it was adding, did you try the second post that I added?
=sumproduct(--('Shrink Lookup'!L3:L50000='CHD Off Phone'!$B20 & "")*('Shrink Lookup'!M3:M50000+0))

 

[ColDrum]

That's great.



Would you elaborate on combining the SumProduct bit with the SumIf bit you have?

The range of times I have are all in column M. I am looking SUMPRODUCT them, if my SUMIF returns true.
I've now had both the SUMIF and the SUMPRODUCT working on their own, but I'm not sure how to combine them.

jamtay - I tried the 2nd version but it doesn't work. I'm not sure where to place the 2nd comman from the SUMIF.

 

[jamtay317]

you can use SumProduct to do both in one forumla please post the sumif formula