How do i sum selected time value

[ichibuzor]

	(AK)	(AL)	(AM)
SUBJECT	HR ACHIEVED	HR NOT ACHIEVED	STANDARD
112)MICROSOFT SQL	5:25	6:35	12:00
113) XCEL OR ACCESS	2:19	0:19	2:00
114)YOUTUBE	0:00	2:00	2:00
115)RESEARCH	1:27	2:33	4:00
116) READING(EXCEL&OTH	1:54	6:06	8:00
117) JUBILEE ASSOCIATES	5:04	1:04	4:00
118) FORBES(READING)	2:17	2:43	5:00
119)CATCH-UP (sat &sun)	0:00	0:00	0:00
SUM 1	18:26	#VALUE!	181:00

<colgroup><col style="mso-width-source:userset;mso-width-alt:6546;width:134pt" width="179"> <col style="mso-width-source:userset;mso-width-alt:5668;width:116pt" width="155"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

THE CODE FOR HOURS NOT ACHIEVED = IF(AM112>AK112,AM112-AK112,AK112-AM112)
THE PROBLEM IS I WISH TO SUM ONLY VALUES (HOURS NOT ACHIEVED) WHERE HOURS ACHIEVED IS GREATER THAN THE STANDARD HOURS E.G AL113+AL117. CAN'T THERE BE ANY AUTOMATED CODE? I TRIED USING THIS SUMIFS(AK112:AM119,AL112:AL119,AK112>AM112,AL112:AL119,AK113>AM113,AL112:AL119,AK114>AM114,AL112:AL119,AK115>AM115,AL112:AL119,AK116>AM116,AL112:AL119,AK117>AM117,AL112:AL119,AK118>AM118,AL112:AL119,AK119>AM119) BUT IT NEVER WORKED. PLEASE HELP ME OUT.
THANKS
REGARDS,
CHIBS

 

[gaz_chops]

Try

=SUMPRODUCT(--(AK112:AK119>AM112:AM119),AL112:AL119)

 

[ichibuzor]

Try

=SUMPRODUCT(--(AK112:AK119>AM112:AM119),AL112:AL119)

Dear Sir,
I tried it but it failed to work.
Secondly, I wanted to find out about the -- indicated above means you are trying to tell the computer to ignore the code (AK112:AK119>AM112:AM119). Am I right? I also tried it removing and putting it but none worked.
Thanks
Kind regards,
Chibs

 

[gaz_chops]

It works fine for me with your sample. When you say fail what answer are you getting?

 

[ichibuzor]

It works fine for me with your sample. When you say fail what answer are you getting?

Dear Sir,
It is giving me 0:00 value. I have tried all possible means but I guess there is a problem somewhere.
Also, what is the use of -- in the code? Could you also explain the code so that I can learn from the expert.

 

[gaz_chops]

The -- forces 1 or 0 instead of True or False. So where Hr achieved is greater than Standard it will return True which becomes 1 that is the multiplied by the value in Hr not achieved, then all the values are summed.

Did you enter the formula exactly as i posted? It works for me!

[TABLE="width: 376"]
<tbody>[TR]
[TD]SUBJECT[/TD]
[TD]HR ACHIEVED[/TD]
[TD]HR NOT ACHIEVED[/TD]
[TD]STANDARD[/TD]
[/TR]
[TR]
[TD]112)MICROSOFT SQL[/TD]
[TD="align: right"]05:25[/TD]
[TD="align: right"]06:35[/TD]
[TD="align: right"]12:00[/TD]
[/TR]
[TR]
[TD]113) XCEL OR ACCESS[/TD]
[TD="align: right"]02:19[/TD]
[TD="align: right"]00:19[/TD]
[TD="align: right"]02:00[/TD]
[/TR]
[TR]
[TD]114)YOUTUBE[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]02:00[/TD]
[TD="align: right"]02:00[/TD]
[/TR]
[TR]
[TD]115)RESEARCH[/TD]
[TD="align: right"]01:27[/TD]
[TD="align: right"]02:33[/TD]
[TD="align: right"]04:00[/TD]
[/TR]
[TR]
[TD]116) READING(EXCEL&OTH[/TD]
[TD="align: right"]01:54[/TD]
[TD="align: right"]06:06[/TD]
[TD="align: right"]08:00[/TD]
[/TR]
[TR]
[TD]117) JUBILEE ASSOCIATES[/TD]
[TD="align: right"]05:04[/TD]
[TD="align: right"]01:04[/TD]
[TD="align: right"]04:00[/TD]
[/TR]
[TR]
[TD]118) FORBES(READING)[/TD]
[TD="align: right"]02:17[/TD]
[TD="align: right"]02:43[/TD]
[TD="align: right"]05:00[/TD]
[/TR]
[TR]
[TD]119)CATCH-UP (sat &sun)[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]00:00[/TD]
[TD="align: right"]00:00[/TD]
[/TR]
[TR]
[TD]SUM 1[/TD]
[TD="align: right"]18:26[/TD]
[TD="align: right"]21:20[/TD]
[TD="align: right"]37:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][U][B][COLOR=#ff0000]1:23[/COLOR][/B][/U][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ichibuzor]

Dear Sir,
Thanks a bunch!!! it worked very well. I was careless with the formula and writing =SUMPRODUCT(--(AK112:AK119>AM112:AM119,AL112:AL119) instead of SUMPRODUCT(--(AK112:AK119>AM112:AM119),AL112:AL119).

Secondly, I will like to know the various kind of business Analysis I could do with for a pharmaceutical business who has wholesale operations and have five stores each with a miniwarehouse and two main warehouse. The company sells different pharmceutical product in wholesale e.g Felgin (pain killer), Mebotex (deworming) etc.
Thanks