Lookup with criteria and give actual sum base on two criteria

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
133
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
BCDEFGHIJKLM
2Criteria 2Criteria 1
3Month Issued Month Voyage Vessel Name Deck Status Departed Rig Name Start Time End Time HoursRig Name Actual Hours
401-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC02/01/21 08:5502/01/21 12:153:20USSC7:25
501-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30Jopetwill-30002/01/21 13:3502/01/21 19:506:15Jopetwill-3006:15
601-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC02/01/21 20:2502/01/21 21:301:05
701-Jan-21Jan-213705ADNOC-810Half01/01/21 09:30USSC03/01/21 07:4503/01/21 10:453:00
801-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Al Ghallan Island 02/01/21 07:1002/01/21 12:185:08Al Ghallan Island 5:08
901-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Asseifiya Island 02/01/21 14:1802/01/21 15:000:42Asseifiya Island 0:42
1001-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Ettouk Island 02/01/21 17:4002/01/21 18:421:02Ettouk Island 1:02
1101-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Umm Al Anbar03/01/21 07:0003/01/21 10:423:42Umm Al Anbar3:42
1201-Jan-21Dec-203706ADNOC-224Half01/01/21 18:48Deep Driller-304/01/21 04:1804/01/21 16:4212:24Deep Driller-312:24
1301-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Artabhatt-129/12/20 08:1529/12/20 15:207:05Rig Artabhatt-121:35
1401-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Al Bzoom30/12/20 15:5030/12/20 21:005:10Rig Al Bzoom
1501-Dec-20Jan-213666-OASL SWIFTHalf02/01/21 16:00Rig Artabhatt-131/12/20 04:0031/12/20 18:3014:30
1601-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Rig Artabhatt-102/01/21 09:4502/01/21 10:551:10
1701-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Barge Pride 02/01/21 13:4002/01/21 16:302:50
1801-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Rig Diyina02/01/21 20:4503/01/21 02:405:55
1901-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Deep Driller-303/01/21 10:1503/01/21 12:101:55
2001-Jan-21Jan-213707Z-POWERHalf01/01/21 19:00Deep Driller-303/01/21 20:1004/01/21 04:007:50
2101-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Rig Hudairiyat02/01/21 16:1804/01/21 16:3048:12
2201-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Rig Hudairiyat04/01/21 21:0005/01/21 11:5414:54
2301-Jan-21Jan-213708SMIT LUZONFull 01/01/21 20:30Barge Shamal15/01/21 21:0016/01/21 01:064:06
2401-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Lulu01/01/21 23:5902/01/21 00:450:46
2501-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Hail02/01/21 11:2502/01/21 13:001:35
2601-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig VKN-302/01/21 22:0002/01/21 23:101:10
2701-Jan-21Jan-213709SMIT LUMUTHalf01/01/21 19:15Rig Al Ghallan03/01/21 08:3504/01/21 03:4519:10
2801-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig SMS Faith 03/01/21 06:0003/01/21 08:482:48
2901-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Al Gharbia03/01/21 12:0003/01/21 21:009:00
3001-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Marawwah04/01/21 00:3504/01/21 14:5014:15
3101-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Yemillah04/01/21 16:3005/01/21 00:017:31
3201-Jan-21Jan-213710B-LIBERTY-313NAF02/01/21 12:45Rig Al Hail05/01/21 05:3005/01/21 20:0014:30
3301-Jan-21Jan-213711MAC PHOENIXHalf01/01/21 21:22Rig Muhaiyimat02/01/21 04:1302/01/21 15:4511:32
3401-Jan-21Jan-213711MAC PHOENIXHalf01/01/21 21:22Deep Driller-302/01/21 17:3003/01/21 12:1518:45
3501-Jan-21Jan-213712ADNOC-812Half01/01/21 21:15Barge Al Hyleh02/01/21 09:4502/01/21 18:108:25
3601-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge MB-102/01/21 10:4502/01/21 15:505:05
3701-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge Pesto03/01/21 08:3003/01/21 13:254:55
3801-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZWSC04/01/21 08:4004/01/21 09:200:40
3901-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZCSC04/01/21 13:4004/01/21 17:454:05
4001-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZCSC04/01/21 15:5304/01/21 17:451:52
4101-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30Barge MB-104/01/21 20:0004/01/21 20:300:30
4201-Jan-21Jan-213713A-HERCULESHalf02/01/21 00:30ZWSC05/01/21 08:4005/01/21 11:453:05
4301-Jan-21Jan-211LCT-TARFFAHFull 03/01/21 07:10Zirku Island03/01/21 20:4004/01/21 08:5012:10
4401-Jan-21Jan-211LCT-TARFFAHFull 03/01/21 07:10Ettouk Island 04/01/21 17:4005/01/21 08:2514:45
4501-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT03/01/21 23:1504/01/21 01:252:10
4601-Jan-21Jan-212ADNOC-850Half03/01/21 13:15UZWSP04/01/21 02:5004/01/21 03:450:55
4701-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT04/01/21 08:3004/01/21 18:3010:00
4801-Jan-21Jan-212ADNOC-850Half03/01/21 13:15ACPT05/01/21 07:2505/01/21 11:504:25
4901-Jan-21Jan-212ADNOC-850Half03/01/21 13:15UZNSP05/01/21 13:0005/01/21 15:302:30
5001-Jan-21Jan-213ADNOC-510Full 02/01/21 14:20Rig Sms Mariam 03/01/21 07:2004/01/21 02:1518:55
5101-Jan-21Jan-214A-GRACEFull 02/01/21 06:15Rig Makasib03/01/21 05:0305/01/21 12:0855:05
5201-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC03/01/21 07:3003/01/21 14:206:50
5301-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC04/01/21 07:4004/01/21 17:309:50
5401-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35Barge Pesto04/01/21 18:0804/01/21 18:550:47
5501-Jan-21Jan-215MARCAP-2Full 02/01/21 17:35ZWSC05/01/21 07:5505/01/21 18:2510:30
5601-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Al Ghallan03/01/21 10:5003/01/21 11:250:35
5701-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Yemillah03/01/21 12:3003/01/21 14:001:30
5801-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Al Hail04/01/21 01:1004/01/21 17:3016:20
5901-Jan-21Jan-216QMS DELTAHalf02/01/21 21:50Rig Makasib05/01/21 19:3006/01/21 07:4012:10
6001-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Qatia Island 03/01/21 19:2003/01/21 22:002:40
6101-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Bu Sikeen Island04/01/21 07:1004/01/21 10:002:50
6201-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Qatia Island 04/01/21 13:4004/01/21 22:308:50
6301-Jan-21Jan-217ADNOC-1011Half03/01/21 03:40Al Ghallan Island 05/01/21 08:4505/01/21 12:003:15
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F4:F63Cell Valuecontains "NAF"textNO
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
133
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS
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, "" )))

Excel Formula:
= 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

Board Regular
Joined
Feb 12, 2020
Messages
133
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2012
Messages
5,261
Office Version
  1. 365
Platform
  1. MacOS
you are welcome, glad it worked OK
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,389
Messages
5,769,798
Members
425,572
Latest member
ja189704

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top