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

 

[ColDrum]

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

Great.
It's the same one I posted before:

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

 

[jamtay317]

try this

=sumproduct(--([COLOR=#333333]'Shrink Lookup'!L:L=[/COLOR][COLOR=#333333]'CHD Off Phone'!$B20)*(([/COLOR][COLOR=#333333]'Shrink Lookup'!M:M)+0))[/COLOR]

 

[ColDrum]

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

try this

=sumproduct(--([COLOR=#333333]'Shrink Lookup'!L:L=[/COLOR][COLOR=#333333]'CHD Off Phone'!$B20)*(([/COLOR][COLOR=#333333]'Shrink Lookup'!M:M)+0))[/COLOR]

Jamtay - that's what you gave before but it doesn't work - I need to know where to put the comma in the SUMIF

 

[jamtay317]

you need to take out &"" or use sumifs, but that forumla works for me

 

[ColDrum]

But what you are providing does not include a SUMIF, which is the problem I am having.

I only want to use the SUMPRODUCT to add up all the times in column M IF column L in the Shrink Lookup sheets contains what is in $B20 from the CHD Off Phone sheet.

 

[Aladin Akyurek]

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.

I'll venture a guess:

=SUMPRODUCT(--('Shrink Lookup'!L2:L500='CHD Off Phone'!$B20 & ""),'Shrink Lookup'!M2:M500+0)

If this works, try to follow the following recipe:

Select an unused cell.
Copy this cell.
Select the M range with time values.
Activate Paste Special.
Choose the Add option.
Activate OK.

After this recipe, the SumIf formula you already have. That is:

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

 

[ColDrum]

Aladin - thanks very much again - that worked perfectly!

Jamtay - thanks for your help too.

 

[Aladin Akyurek]

Aladin - thanks very much again - that worked perfectly!

Jamtay - thanks for your help too.

You are welcome. Thanks for providing feedback.

 

[Giridhar]

You are welcome. Thanks for providing feedback.

I ran into a similar issue. When I run a pivot table, the fields appear 0:00:00. Time on working sheet is formatted as [h]:mm:ss. I changed the pivot table cells to [h]:mm:ss but issue persists.

SUMPRODUCT(A1:A5+0) gives me the correct addition of time but it is too tedious for me to do for the entire sheet.

 

[Aladin Akyurek]

I ran into a similar issue. When I run a pivot table, the fields appear 0:00:00. Time on working sheet is formatted as [h]:mm:ss. I changed the pivot table cells to [h]:mm:ss but issue persists.

SUMPRODUCT(A1:A5+0) gives me the correct addition of time but it is too tedious for me to do for the entire sheet.

Run the following procedure on the source data:

Select an unused cell.
Copy this cell.
Select the source data range with time values.
Activate Paste Special.
Choose the Add option.
Activate OK.

Now refresh the pivot table(s). Does this help?