Sum a vertical range if it is matching a horizontal range

[asad]

Hello All,

I have something like this:

60501	06:50		OFF	OFF	60506	60509	60506	60509	60506	15:36
60502	09:15		0:00	0:00	10:26	5:10	10:26	5:10	10:26	41:38
60503	08:45
60504	08:45
60505	09:16
60506	10:26
60507	10:20
60508	07:35
60509	05:10
60510	08:15
61001	07:30
61002	04:00
61003	05:00
61004	05:30
61005	07:30
61006	09:30
62001	04:00
62002	04:00
62003	04:00
62004	04:30
62005	04:30
62006	07:30
62007	08:30
62008	08:00
62009	07:30
62010	08:30
OFF	00:00

<colgroup><col><col><col><col span="7"><col></colgroup><tbody>
</tbody>

The formula I have used is :

{=SUM(IF(ISNUMBER(MATCH($B$2:$B$27,$E$2:$K$2,0)),$C$2:$C$27))}

with ctrl+shift+enter
But I am getting the wrong answer 15:36. How can I change the formula to get correct answer of 41:38

Regards and many Thanks

Asad

 

[asad]

Got it,
Simple formula

{=SUM(IF($B$2:$B$28=E2:K2,$C$2:$C$28))}

did the trick.
Sometimes you have to take a simpler path rather than a complicated one.