Lookup with criteria and give actual sum base on two criteria

[Lukma]

Hi Guys

well am trouble today as my boss ask me to update my data in a way to sum the actual of time spent for each each location as per voyage in other column

And i have created my data in this way that in Column L4 and M4 which i have giving a sample

So i need a solution that extract unique Rig Name in Column L4 and to give me the actual spent hours in column M4 , i will appreciate if i can get a solution to this trouble

Regards

If there is a formula to ease my stress i will be more glade with solution

Book2
	B	C	D	E	F	G	H	I	J	K	L	M
2			Criteria 2				Criteria 1
3	Month	Issued Month	Voyage	Vessel Name	Deck Status	Departed	Rig Name	Start Time	End Time	Hours	Rig Name	Actual Hours
4	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	02/01/21 08:55	02/01/21 12:15	3:20	USSC	7:25
5	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	Jopetwill-300	02/01/21 13:35	02/01/21 19:50	6:15	Jopetwill-300	6:15
6	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	02/01/21 20:25	02/01/21 21:30	1:05
7	01-Jan-21	Jan-21	3705	ADNOC-810	Half	01/01/21 09:30	USSC	03/01/21 07:45	03/01/21 10:45	3:00
8	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Al Ghallan Island	02/01/21 07:10	02/01/21 12:18	5:08	Al Ghallan Island	5:08
9	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Asseifiya Island	02/01/21 14:18	02/01/21 15:00	0:42	Asseifiya Island	0:42
10	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Ettouk Island	02/01/21 17:40	02/01/21 18:42	1:02	Ettouk Island	1:02
11	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Umm Al Anbar	03/01/21 07:00	03/01/21 10:42	3:42	Umm Al Anbar	3:42
12	01-Jan-21	Dec-20	3706	ADNOC-224	Half	01/01/21 18:48	Deep Driller-3	04/01/21 04:18	04/01/21 16:42	12:24	Deep Driller-3	12:24
13	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Artabhatt-1	29/12/20 08:15	29/12/20 15:20	7:05	Rig Artabhatt-1	21:35
14	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Al Bzoom	30/12/20 15:50	30/12/20 21:00	5:10	Rig Al Bzoom
15	01-Dec-20	Jan-21	3666-O	ASL SWIFT	Half	02/01/21 16:00	Rig Artabhatt-1	31/12/20 04:00	31/12/20 18:30	14:30
16	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Rig Artabhatt-1	02/01/21 09:45	02/01/21 10:55	1:10
17	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Barge Pride	02/01/21 13:40	02/01/21 16:30	2:50
18	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Rig Diyina	02/01/21 20:45	03/01/21 02:40	5:55
19	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Deep Driller-3	03/01/21 10:15	03/01/21 12:10	1:55
20	01-Jan-21	Jan-21	3707	Z-POWER	Half	01/01/21 19:00	Deep Driller-3	03/01/21 20:10	04/01/21 04:00	7:50
21	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Rig Hudairiyat	02/01/21 16:18	04/01/21 16:30	48:12
22	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Rig Hudairiyat	04/01/21 21:00	05/01/21 11:54	14:54
23	01-Jan-21	Jan-21	3708	SMIT LUZON	Full	01/01/21 20:30	Barge Shamal	15/01/21 21:00	16/01/21 01:06	4:06
24	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Lulu	01/01/21 23:59	02/01/21 00:45	0:46
25	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Hail	02/01/21 11:25	02/01/21 13:00	1:35
26	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig VKN-3	02/01/21 22:00	02/01/21 23:10	1:10
27	01-Jan-21	Jan-21	3709	SMIT LUMUT	Half	01/01/21 19:15	Rig Al Ghallan	03/01/21 08:35	04/01/21 03:45	19:10
28	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig SMS Faith	03/01/21 06:00	03/01/21 08:48	2:48
29	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Al Gharbia	03/01/21 12:00	03/01/21 21:00	9:00
30	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Marawwah	04/01/21 00:35	04/01/21 14:50	14:15
31	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Yemillah	04/01/21 16:30	05/01/21 00:01	7:31
32	01-Jan-21	Jan-21	3710	B-LIBERTY-313	NAF	02/01/21 12:45	Rig Al Hail	05/01/21 05:30	05/01/21 20:00	14:30
33	01-Jan-21	Jan-21	3711	MAC PHOENIX	Half	01/01/21 21:22	Rig Muhaiyimat	02/01/21 04:13	02/01/21 15:45	11:32
34	01-Jan-21	Jan-21	3711	MAC PHOENIX	Half	01/01/21 21:22	Deep Driller-3	02/01/21 17:30	03/01/21 12:15	18:45
35	01-Jan-21	Jan-21	3712	ADNOC-812	Half	01/01/21 21:15	Barge Al Hyleh	02/01/21 09:45	02/01/21 18:10	8:25
36	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge MB-1	02/01/21 10:45	02/01/21 15:50	5:05
37	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge Pesto	03/01/21 08:30	03/01/21 13:25	4:55
38	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZWSC	04/01/21 08:40	04/01/21 09:20	0:40
39	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZCSC	04/01/21 13:40	04/01/21 17:45	4:05
40	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZCSC	04/01/21 15:53	04/01/21 17:45	1:52
41	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	Barge MB-1	04/01/21 20:00	04/01/21 20:30	0:30
42	01-Jan-21	Jan-21	3713	A-HERCULES	Half	02/01/21 00:30	ZWSC	05/01/21 08:40	05/01/21 11:45	3:05
43	01-Jan-21	Jan-21	1	LCT-TARFFAH	Full	03/01/21 07:10	Zirku Island	03/01/21 20:40	04/01/21 08:50	12:10
44	01-Jan-21	Jan-21	1	LCT-TARFFAH	Full	03/01/21 07:10	Ettouk Island	04/01/21 17:40	05/01/21 08:25	14:45
45	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	03/01/21 23:15	04/01/21 01:25	2:10
46	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	UZWSP	04/01/21 02:50	04/01/21 03:45	0:55
47	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	04/01/21 08:30	04/01/21 18:30	10:00
48	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	ACPT	05/01/21 07:25	05/01/21 11:50	4:25
49	01-Jan-21	Jan-21	2	ADNOC-850	Half	03/01/21 13:15	UZNSP	05/01/21 13:00	05/01/21 15:30	2:30
50	01-Jan-21	Jan-21	3	ADNOC-510	Full	02/01/21 14:20	Rig Sms Mariam	03/01/21 07:20	04/01/21 02:15	18:55
51	01-Jan-21	Jan-21	4	A-GRACE	Full	02/01/21 06:15	Rig Makasib	03/01/21 05:03	05/01/21 12:08	55:05
52	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	03/01/21 07:30	03/01/21 14:20	6:50
53	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	04/01/21 07:40	04/01/21 17:30	9:50
54	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	Barge Pesto	04/01/21 18:08	04/01/21 18:55	0:47
55	01-Jan-21	Jan-21	5	MARCAP-2	Full	02/01/21 17:35	ZWSC	05/01/21 07:55	05/01/21 18:25	10:30
56	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Al Ghallan	03/01/21 10:50	03/01/21 11:25	0:35
57	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Yemillah	03/01/21 12:30	03/01/21 14:00	1:30
58	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Al Hail	04/01/21 01:10	04/01/21 17:30	16:20
59	01-Jan-21	Jan-21	6	QMS DELTA	Half	02/01/21 21:50	Rig Makasib	05/01/21 19:30	06/01/21 07:40	12:10
60	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Qatia Island	03/01/21 19:20	03/01/21 22:00	2:40
61	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Bu Sikeen Island	04/01/21 07:10	04/01/21 10:00	2:50
62	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Qatia Island	04/01/21 13:40	04/01/21 22:30	8:50
63	01-Jan-21	Jan-21	7	ADNOC-1011	Half	03/01/21 03:40	Al Ghallan Island	05/01/21 08:45	05/01/21 12:00	3:15
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:F63	Cell Value	contains "NAF"	text	NO

 

[etaf]

Thats Just 16:00 if FULL & 10:00 if HALF

Whats the set of rules for KPI of 10

B column = HALF AND C column = Rigs&BARGES AND E column >10 , then Subtract E from 10


Whats the set of rules for 16

LIST like 10

I need to see the rules in words
B column = HALF AND C column = Rigs&BARGES AND E column >10 , then Subtract E from 10

 

[Lukma]

Thanks

B Column = Full AND C column = Rig & Barge And E Column > 16, Then Subtract From 16
B Column = Half AND C column = Rig & Barge And E Column > 10, Then Subtract From 10
C column = Drl-Island AND E Column > 10, then Subtract > Then Subtract From 10
C column = Prd-Island AND E Column > 10, then Subtract > Then Subtract From 10
C column = Prd-Complex AND E Column > 10, then Subtract > Then Subtract From 12

I hope am able to get it right as you have ask

Thanks once again for your patient and helping out to resolve my trouble. i really appreciate.

 

[etaf]

not sure what this bit is, you may have missed something

then Subtract > Then Subtract

BUT
lets work on the nested IF()
As you have 2019 version you may be able to use IFS()
BUT I'll stick with IF for now, and do a bit at a time, so you see how constructed

I need to a solution in Column F11 with IF formula

B Column = Full AND C column = Rig & Barge And E Column > 16, Then Subtract From 16

IN F11 then
This is the test
AND ( B11 = "FULL" , C11 = "Rig&Barge" , E11 > 16 )
TRUE part would be
E11 - 16

so put together for a TRUE
= IF ( AND ( B11 = "FULL" , C11 = "Rig&Barge" , E11 > 16 ), E11 - 16 ,
NOW the false bit is another IF

B Column = Half AND C column = Rig & Barge And E Column > 10, Then Subtract From 10

IF ( AND ( B11 = "HALF" , C11 = "Rig&Barge" , E11 > 10 ) , E11 - 10

SO

= IF ( AND ( B11 = "FULL" , C11 = "Rig&Barge" , E11 > 16 ), E11 - 16 , IF ( AND ( B11 = "HALF" , C11 = "Rig&Barge" , E11 > 10 ) , E11 - 10 ,

C column = Drl-Island AND E Column > 10, then Subtract > Then Subtract From 10

IF ( AND ( C11 = "Drl-Island", E11 > 10 ) , E11 -10
BUT as we have 3 conditions to provide E11 - 10 , we can use an OR to combine

=IF ( OR ( AND ( B11 = "HALF" , C11 = "Rig&Barge" , E11 > 10 ) , AND ( C11 = "Drl-Island", E11 > 10 ) , AND ( C11 = "Prd-Island" , E11 > 10) ) , E11 -10 ,
combined
= IF ( AND ( B11 = "FULL" , C11 = "Rig&Barge" , E11 > 16 ), E11 - 16 , IF ( OR ( AND ( B11 = "HALF" , C11 = "Rig&Barge" , E11 > 10 ) , AND ( C11 = "Drl-Island", E11 > 10 ) , AND ( C11 = "Prd-Island" , E11 > 10) ) , E11 -10 ,
Now the 12 bit

C column = Prd-Complex AND E Column > 10, then Subtract > Then Subtract From 12

AND ( C11 = "Prd-Complex" , E11 > 12 ) , E11 -12

= IF ( AND ( B11 = "FULL" , C11 = "Rig&Barge" , E11 > 16 ), E11 - 16 , IF ( OR ( AND ( B11 = "HALF" , C11 = "Rig&Barge" , E11 > 10 ) , AND ( C11 = "Drl-Island", E11 > 10 ) , AND ( C11 = "Prd-Island" , E11 > 10) ) , E11 -10 , IF ( AND ( C11 = "Prd-Complex" , E11 > 12 ) , E11 -12, "" )))

= IF ( AND ( B11 = "FULL" ,  C11 = "Rig&Barge" ,  E11 > 16 ), E11 - 16 , IF ( OR (  AND ( B11 = "HALF" ,  C11 = "Rig&Barge" ,  E11 > 10 )  , AND ( C11 = "Drl-Island", E11 > 10 )  , AND ( C11 = "Prd-Island" ,  E11 > 10) ) ,  E11 -10 , IF ( AND ( C11 = "Prd-Complex" , E11 > 12 )  , E11 -12, "" )))

This could be simplified a lot , BUT i will keep as that , so you can see the steps and how its built up logically

 

[Lukma]

Thanks so Much

Yes i could not think of this but it works out just as i need
Thanks so much Etaf really i appreciate you effort in resolving my day trouble

Thanks

 

[etaf]

you are welcome, glad it worked OK