Sumif with time HH:MM - not working right

[hoshino]

I am using sumif all the time with pounds - I have to use with time but the results aren't right...


its timesheets time:

Sheet 1

[Column A] multiple duplicate staff names (format text)

[Column B] Start time (format hh:mm)

[Column C] End time (format hh:mm)

[Column D] =C-B for total hours worked (format hh:mm)


Sheet 2

[Column A] names from sheet one without duplicate (format text)

[Column B] =SUMIF('Sheet1'!A:A, 'Sheet2'!A1, Sheet1!D:D) (format hh:mm)


If I manually sum the first name, total is 45.30, my sumif gives me 21.59.


I have checked and tried a number of things but to no avail, does anyone understand what is going wrong?


Many thanks in advance.

 

[Hui]

Hoshino


Firstly, Welcome to the Chandoo.org forums.


Have you tried formatting the result cell as [h]:mm ?


If that doesn't help can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[sdsurzh]

Hi


Let us assume the data the below format

starting from A1 in sheet1


Col A Col B Col C Cal D

name Start Time Stop Time Time Taken

Kumar 13:53 15:32 01:39

Kumar 15:32 17:54 02:21

Kumar 11:41 15:29 03:47

Kumar 15:29 16:57 01:27

Kumar 16:57 18:06 01:08

Raja 11:47 12:07 00:20

Raja 12:08 14:59 02:50

Raja 14:59 17:54 02:54

Raja 17:54 18:35 00:40

Raja 11:02 16:03 05:00

Raja 16:03 16:51 00:47

Suresh 16:51 17:14 00:22

Suresh 17:15 18:26 01:10

Suresh 18:49 19:01 00:11

Suresh 10:57 14:48 03:51

Suresh 14:48 17:57 03:09

Suresh 11:29 14:32 03:02

Suresh 14:39 17:01 02:22

Suresh 17:27 18:45 01:18


In sheet2 you want the sum of unique names like:

Col A Col B

name Total Time

Kumar 10:25

Raja 12:34

Suresh 15:28


In sheet2 select range "B2" and apply the following formula


=SUMIF(Sheet1!$A$1:$A$20,Sheet2!$A2,Sheet1!$D$1:$D$20)


Thanks,

Suresh Kumar S

 

[Faseeh]

Hi Suresh Kumar,


You sumif formula is giving correct result have. see this file:


http://dl.dropbox.com/u/60644346/Suresh%20Kumar.xlsx


Regards,

 

[Debraj]

@ Hoshino,

I guess, Your Problem is not with SumIf (as it giving you result 21.59) or Format Text (as actual total is 45.30 less is less than 60.00) .. Your problem is in A:A..

Please create a additional column, lets say E where use formula

=Trim(A1)

Now try to use SumIf as

=SUMIF('Sheet1'!E:E, 'Sheet2'!A1, Sheet1!D:D)

Otherwise, hope you have already guessed where I want to give you a look.

Search for the word looks like 'Sheet2'!A1 and with a value (45.30-21.59)..


@ Faseeh,

Eid Mubaraak..

Problem is not with Suresh..


Regards,

Deb

 

[hoshino]

Dear All


Thank you for the welcome and for the three kind suggestions.


I went through in order, and luckily [h]:mm did it (Big respects to Hui-san - can't be helped guys - he is the ninja!)


I've seen this before but completely forgotten about it - its for when times go over 24 hours right - excel will go back to 0 when going over 24 hours with hh:mm - with [h]:mm it keeps on adding.... (sound right?)


Thank you all for your Excel passion regardless.


Ah what a wieght off my shoulders thank you!