SUMIFS based on multiple criteria and between date

[Lukma]

Good day friends

I need to help out with a formula on my data sheet the problem is the given tasks and its required and doing so it will take a long and stressful time
so i need a formula that and sum total for specific criteria between date rage.

From Column A i have all Vessel names listed
From Column B Activity Range
From Column D to Colum AH i have date numbers
Now
Column AL i have Vessel Name
Column AM Start Date
Column AN Return Date
From AO to AX i have list criteria Name from Column B Activities

So i need to sumifs based on multiple criteria between dates
So let say from Column AM date 2 and AN 7
then i need to sum Column D date 2 to 7 all time based on Criteria

Thank i will appreciate any one with helping and and i hope am able to explain better

regards

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
1	Vessel Names		Operational Month & Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Total / Hrs	In Days		Vessel Namee	Start Date & Time (Arrival Time at MOSB)	Return to MOSB Date & Time	Waiting VTS	Waiting COP MOSB	Working MOSB	Steaming	Working Offshore	Working Others Marine Supply	Waiting Offshore	Waiting Daylight	Waiting Weather	Maintenance
2	A-CHLOE	Working MOSB	ADNOC Base Jetty Cargo Operation			15.4							15	5.3			14.5	1.2			11					14.4				12.9	2.8				92.5	3.85		A-CHLOE	02/01/23 06:20	07/01/23 01:20
3	A-CHLOE	Waiting COP MOSB	Waitng ADNOC Berthing Instruction																																0	0.00		A-CHLOE	07/01/23 01:20	11/01/23 18:30
4	A-CHLOE	Waiting COP MOSB	Waiting Base Cargo Operation																																0	0.00		A-CHLOE	11/01/23 18:30	19/01/23 01:10
5	A-CHLOE	Waiting ADOF Supplier	Waitng Drilling Material																																0	0.00		A-CHLOE	19/01/23 01:10	25/01/23 08:50
6	A-CHLOE	Waiting COP MOSB	Cargo Operation Suspended																																0	0.00		A-CHLOE	25/01/23 08:50	02/02/23 07:00
7	A-CHLOE	Waiting VTS	Waiting VTS permission		1.6												4.3			8															13.9	0.58		A-GRACE	01/01/23 10:20	06/01/23 06:50
8	A-CHLOE	Waiting VTS	Waiting on Channel Closure																																0	0.00		A-GRACE	06/01/23 06:50	10/01/23 01:25
9	A-CHLOE	Waiting Weather	Waitinhg WOW , FOG / POOR VISIBILITY																																0	0.00		A-GRACE	10/01/23 01:25	19/01/23 08:00
10	A-CHLOE	Steaming	In Transit / Steaming to Location		3.1	8.6	7							11.4			3.9	11.8			13	0.5				3.3	7.5				9.9				80	3.33		A-GRACE	19/01/23 08:00	25/01/23 01:30
11	A-CHLOE	Working Free-Port	Free-Port / Mus-Port																																0	0.00		A-GRACE	25/01/23 01:30	31/01/23 18:25
12	A-CHLOE	Waiting COP Free-Port	Standby location ( Waiting )																																0	0.00		A-GRACE	31/01/23 18:25	03/02/23 23:10
Sheet1

Cell Formulas
Range	Formula
AI2:AI12	AI2	=SUM(D2:AH2)
AJ2:AJ12	AJ2	=AI2/24

 

[Lukma]

you seem to be using a different formula now

=IFERROR(1/(1/SUMPRODUCT(($E$2:$AI$1728)*($A$2:$A$1728=$AL2)*($B$2:$B$1728=$AM2)*($C$2:$C$1728=AP$1)*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<=DAY($AO2))))),"") add up the values in $E$2:$AI$1728 which comes to - 451.6 but only if column a = al2 = 1st jan 23 =SUMPRODUCT((E2:AI17)*(A2:A17=AL2)) which a2 to a17 is - so stll 451.6 =SUMPRODUCT((E2:AI17)*(A2:A17=AL2)*($B$2:$B$17=$AM2)) now only if b has am2 LCT ADNOC-1010 they all do - so stll 451.6 next column c = ap1 Channel Waiting Time & VTS Delay so now result is 5.9 next $E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<=DAY($AO2))))), day an2 = 1 day ao2 = 5 still 5.9 , as that intered for day 2 so still 5.9 1/1/5.9 = 0.169491525 not sure on question if i change an2 - so its the 2nd and also ao2 to 2nd still works Book3 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA 1 Vessel Names Cri Operational Month & Date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Total / Hrs In Days Vessel Namee Start Date & Time (Arrival Time at MOSB) Return to MOSB Date & Time Channel Waiting Time & VTS Delay Waiting Cargo Operation & Berth (MOSB Base) Work Time at MOSB Total Steaming Infield Steaming Total Working Hours at Offshore Locations Working Others Marine Supply Waiting Time at Offshore Lcoations Waiting Daylight Complex Waiting On Weather Maintenance Days 2 1/1/23 LCT ADNOC-1010 Work Time at MOSB ADNOC Base Jetty Cargo Operation 0.8 10.2 0.7 9.9 10.3 7.6 0.3 10.5 3.2 1.5 11.9 3.9 1.5 72.3 3.0125 Sunday 1 January 2023 LCT ADNOC-1010 Monday 2 January 2023 Monday 2 January 2023 0.245833333 0.1375 0.425 0.191666667               0.169491525 Sheet1 Cell Formulas Range Formula AJ2 AJ2 =SUM(E2:AI2) AK2 AK2 =AJ2/24 AL2 AL2 =IFERROR(EOMONTH(AN2,-1)+1,"") AP2:AZ2 AP2 =IFERROR(1/(1/SUMPRODUCT(($E$2:$AI$1728)*($A$2:$A$1728=$AL2)*($B$2:$B$1728=$AM2)*($C$2:$C$1728=AP$1)*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<=DAY($AO2)))))/24,"") BA2 BA2 =1/1/(SUMPRODUCT((E2:AI17)*(A2:A17=AL2)*($B$2:$B$17=$AM2)*($C$2:$C$17=AP$1)*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<=DAY($AO2)))))

good morning etaf

Thanks for your pronto feedback and guidelines let me insert it and see how result goes
many thanks
with
regards

 

[Lukma]

Good Morning Etaf

Thank you, however, to answer and provide more details I need the formula result to extract and be able to capture what I need within the date
I have highlighted each date range I need the extraction with the formula.

For example, Column=AN has the date of 1/01/2023 and Column=AO is 05/01/2023 is the original date range, but to get and capture the right statistics, I need to minus one day from the return day
so I would want the formula to do this, to always calculate from the start date days and the minus one day from the Return date then i can get the actual and extract all entries for each month

I have manually Do sum example of what I need a formula to sum up based on the criteria name and from the date range


Regards

Working.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
1		Vessel Names	Cri	Operational Month & Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Total / Hrs	In Days		Vessel Namee	Start Date & Time (Arrival Time at MOSB)	Return to MOSB Date & Time	Channel Waiting Time & VTS Delay	Waiting Cargo Operation & Berth (MOSB Base)	Work Time at MOSB	Total Steaming	Infield Steaming	Total Working Hours at Offshore Locations	Working Others Marine Supply	Waiting Time at Offshore Lcoations	Waiting Daylight Complex	Waiting On Weather	Maintenance Days
2	January	LCT ADNOC-1010	Work Time at MOSB	ADNOC Base Jetty Cargo Operation	0.8	10.2			0.7	9.9			10.3					7.6	0.3			10.5	3.2		1.5	11.9			3.9						1.5	72.3	3.01	January	LCT ADNOC-1010	01/01/23 22:50	05/01/23 22:30	0.21	0.13	0.46	1.84		0.71		0.30		0.21
3	January	LCT ADNOC-1010	Waiting Cargo Operation & Berth (MOSB Base)	Waitng ADNOC Berthing Instruction					1.9												5.3							10.9							11.7	29.8	1.24	January	LCT ADNOC-1010	05/01/23 22:30	09/01/23 06:50		0.34	0.42	2.01		0.67		0.21		0.25
4	January	LCT ADNOC-1010	Waiting Cargo Operation & Berth (MOSB Base)	Waiting Base Cargo Operation		3.3			0.4									4.6			3.3	5.6		0.2	0.6	9.7			11							38.7	1.61	January	LCT ADNOC-1010	09/01/23 06:50	14/01/23 10:20		0.21	0.42	1.42		0.46		1.46		0.96
5	January	LCT ADNOC-1010	Waiting Time at Offshore Lcoations	Waitng Drilling Material																																0	0.00	January	LCT ADNOC-1010	14/01/23 10:20	17/01/23 20:40		0.17	0.30	1.21		0.50		0.21		0.46
6	January	LCT ADNOC-1010	Waiting Cargo Operation & Berth (MOSB Base)	Cargo Operation Suspended						6.3			5.3																							11.6	0.48	January	LCT ADNOC-1010	17/01/23 20:40	21/01/23 21:55		0.59	0.63	1.42		0.17		0.05		1.13
7	January	LCT ADNOC-1010	Channel Waiting Time & VTS Delay	Waiting VTS permission																																0	0.00	January	LCT ADNOC-1010	21/01/23 21:55	25/01/23 01:35		0.88	0.54	1.75		0.59		0.17
8	January	LCT ADNOC-1010	Channel Waiting Time & VTS Delay	Waiting on Channel Closure		5.9																														5.9	0.25	January	LCT ADNOC-1010	25/01/23 01:35	31/01/23 10:20		0.46	0.13	0.75		0.26		0.29		4.04
9	January	LCT ADNOC-1010	Waiting On Weather	Waitinhg WOW , FOG / POOR VISIBILITY															11.6			7.5	19.5						5.8	23.6	23.4	24	20.3			135.7	5.65	January	LCT ADNOC-1010	31/01/23 10:20	04/02/23 02:05		0.46	0.05	0.42
10	January	LCT ADNOC-1010	Total Steaming	In Transit / Steaming to Location		4.6			4.7	7.8			8.4					1.5	11.9		3.9	0.4	1.3	12.5		2.4	12.5	3.8	3.3	0.4	0.6		3.7	8.3	0.5	92.5	3.85	February	LCT ADNOC-1010	04/02/23 02:05	07/02/23 01:40
11	January	LCT ADNOC-1010	Working Free Port	Free-Port / Mus-Port																																0	0.00	February	LCT ADNOC-1010	07/02/23 01:40	12/02/23 02:10
12	January	LCT ADNOC-1010	Waiting on Free port	Standby location ( Waiting )																																0	0.00	February	LCT ADNOC-1010	12/02/23 02:10	15/02/23 18:45
13	January	LCT ADNOC-1010	Waiting On Weather	Waitinhg WOW , FOG / POOR VISIBILITY																																0	0.00	February	LCT ADNOC-1010	15/02/23 18:45	23/02/23 22:50
14	January	LCT ADNOC-1010	Total Steaming	In Transit / Steaming to Location																																0	0.00	February	LCT ADNOC-1010	23/02/23 22:50	28/02/23 02:25
15	January	LCT ADNOC-1010	Total Working Hours at Offshore Locations	Asseifiya Island				0.4			2.4					5																				7.8	0.33	February	LCT ADNOC-1010	28/02/23 02:25	03/03/23 05:00
16	January	LCT ADNOC-1010	Total Working Hours at Offshore Locations	Ettouk Island	5.9		6.4		2.7								2.4																			17.4	0.73	March	LCT ADNOC-1010	03/03/23 05:00	06/03/23 15:00
17	January	LCT ADNOC-1010	Total Working Hours at Offshore Locations	Al Ghallan Island							3.2	2.5		0.3		3.6				3.9				4.7	5.7		9.2							6.8		39.9	1.66	March	LCT ADNOC-1010	06/03/23 15:00	11/03/23 12:50
18	January	LCT ADNOC-1010	Total Working Hours at Offshore Locations	Umm Al Anbar				4.9				5.8								8.6																19.3	0.80	March	LCT ADNOC-1010	11/03/23 12:50	15/03/23 19:55
19	January	LCT ADNOC-1010	Total Working Hours at Offshore Locations	Al Qatia Island																																0	0.00	March	LCT ADNOC-1010	15/03/23 19:55	19/03/23 14:55
20	January	LCT ADNOC-1010	Total Working Hours at Offshore Locations	Bu Sikeen Island																																0	0.00	March	LCT ADNOC-1010	19/03/23 14:55	25/03/23 02:10
21	January	LCT ADNOC-1010	Working Others Marine Supply	Others Supply of Vessel (WO/DO)																																0	0.00	March	LCT ADNOC-1010	25/03/23 02:10	29/03/23 10:10
22	January	LCT ADNOC-1010	Waiting Time at Offshore Lcoations	Standby waiting Instruction																																0	0.00	March	LCT ADNOC-1010	29/03/23 10:10	07/04/23 05:25
23	January	LCT ADNOC-1010	Waiting Time at Offshore Lcoations	Waitinhg Cargo Operation	3.6		1.1	3.1	1.7		1.7	1.9		0.3	24	10.3	0.6		0.2	5.3				1.8	1.8		1.7	0.5						7.1		66.7	2.78	April	LCT ADNOC-1010	07/04/23 05:25	09/04/23 16:25
24	January	LCT ADNOC-1010	Waiting On Weather	Waitinhg WOW , FOG / POOR VISIBILITY			5.7				6.1			9.3			14.1																			35.2	1.47	April	LCT ADNOC-1010	09/04/23 16:25	13/04/23 08:20
25	January	LCT ADNOC-1010	Total Steaming	In Transit / Steaming to Location	13.7		10.8	15.6	11.9		10.6	13.8		14.1		5.1	6.9	10.3		6.2	11.5			4.8	14.4		0.6	8.8						1.8	10.3	171.2	7.13	April	LCT ADNOC-1010	13/04/23 08:20	19/04/23 17:40
Sheet1 (2)

Cell Formulas
Range	Formula
AJ2:AJ25	AJ2	=SUM(E2:AI2)
AK2:AK25	AK2	=AJ2/24
AL2:AL23	AL2	=IFERROR(EOMONTH(AN2,-1)+1,"")
AL25	AL25	=IFERROR(EOMONTH(AN24,-1)+1,"")

 

[etaf]

dont use the equal to for AO cell
=IFERROR(1/(1/SUMPRODUCT(($E$2:$AI$1728)*($A$2:$A$1728=$AL2)*($B$2:$B$1728=$AM2)*($C$2:$C$1728=AP$1)*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<=DAY($AO2))))),"")
change to
=IFERROR(1/(1/SUMPRODUCT(($E$2:$AI$1728)*($A$2:$A$1728=$AL2)*($B$2:$B$1728=$AM2)*($C$2:$C$1728=AP$1)*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<DAY($AO2))))),"")

For example, Column=AN has the date of 1/01/2023 and Column=AO is 05/01/2023 is the original date range, but to get and capture the right statistics, I need to minus one day from the return day

Now instead of , in the example , < or = 5
it will just be < 5
so 1 to 4 in that case

 

[Lukma]

dont use the equal to for AO cell
=IFERROR(1/(1/SUMPRODUCT(($E$2:$AI$1728)*($A$2:$A$1728=$AL2)*($B$2:$B$1728=$AM2)*($C$2:$C$1728=AP$1)*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<=DAY($AO2))))),"")
change to
=IFERROR(1/(1/SUMPRODUCT(($E$2:$AI$1728)*($A$2:$A$1728=$AL2)*($B$2:$B$1728=$AM2)*($C$2:$C$1728=AP$1)*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<DAY($AO2))))),"")


Now instead of, in the example, < or = 5
it will just be < 5
so 1 to 4 in that case

Dear Etaf

Thank you, I did the same but I noticed dates 31/01/2023 to 04/01/2023 didn't pick any entry since is <, I believed it should pick the only entry for 31/01/2023

is there a means to get the last date after updating the formula 31/01/2023 not picking

Appreciate your help if anything can be done to have picked anytime is return date moved to the following month

Regards

 

[etaf]

no it will not pick up 31st as its not between the number 1 and 4
the formula is not looking at dates , as DAY , just changes to a number and so the formula part
*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<DAY($AO2))))),"")
is JUST a number between AN2 and AO2

and so to capture those dates will need a rewrite of the formula

I probably need examples of how it would work NOW with dates ...... and NOT numbers ( DAYS() )

 

[Lukma]

no it will not pick up 31st as its not between the number 1 and 4
the formula is not looking at dates , as DAY , just changes to a number and so the formula part
*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<DAY($AO2))))),"")
is JUST a number between AN2 and AO2

and so to capture those dates will need a rewrite of the formula

I probably need examples of how it would work NOW with dates ...... and NOT numbers ( DAYS() )

Good Day Etaf
Thanks for the quick response and I appreciate you looking into this for me, however, Please assist with a better option if it needs you to make some changes in the sheet.

Please if it requires me to change the day range from column E to AI to Date format I am willing to do that if that will get it worked out.

and if you have a better solution to work on the sheet sent previously I would appreciate that would be the best option for me.

i really appreciate all effort and help

Thanks

 

[Lukma]

you seem to be using a different formula now

=IFERROR(1/(1/SUMPRODUCT(($E$2:$AI$1728)*($A$2:$A$1728=$AL2)*($B$2:$B$1728=$AM2)*($C$2:$C$1728=AP$1)*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<=DAY($AO2))))),"") add up the values in $E$2:$AI$1728 which comes to - 451.6 but only if column a = al2 = 1st jan 23 =SUMPRODUCT((E2:AI17)*(A2:A17=AL2)) which a2 to a17 is - so stll 451.6 =SUMPRODUCT((E2:AI17)*(A2:A17=AL2)*($B$2:$B$17=$AM2)) now only if b has am2 LCT ADNOC-1010 they all do - so stll 451.6 next column c = ap1 Channel Waiting Time & VTS Delay so now result is 5.9 next $E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<=DAY($AO2))))), day an2 = 1 day ao2 = 5 still 5.9 , as that intered for day 2 so still 5.9 1/1/5.9 = 0.169491525 not sure on question if i change an2 - so its the 2nd and also ao2 to 2nd still works Book3 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA 1 Vessel Names Cri Operational Month & Date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Total / Hrs In Days Vessel Namee Start Date & Time (Arrival Time at MOSB) Return to MOSB Date & Time Channel Waiting Time & VTS Delay Waiting Cargo Operation & Berth (MOSB Base) Work Time at MOSB Total Steaming Infield Steaming Total Working Hours at Offshore Locations Working Others Marine Supply Waiting Time at Offshore Lcoations Waiting Daylight Complex Waiting On Weather Maintenance Days 2 1/1/23 LCT ADNOC-1010 Work Time at MOSB ADNOC Base Jetty Cargo Operation 0.8 10.2 0.7 9.9 10.3 7.6 0.3 10.5 3.2 1.5 11.9 3.9 1.5 72.3 3.0125 Sunday 1 January 2023 LCT ADNOC-1010 Monday 2 January 2023 Monday 2 January 2023 0.245833333 0.1375 0.425 0.191666667               0.169491525 Sheet1 Cell Formulas Range Formula AJ2 AJ2 =SUM(E2:AI2) AK2 AK2 =AJ2/24 AL2 AL2 =IFERROR(EOMONTH(AN2,-1)+1,"") AP2:AZ2 AP2 =IFERROR(1/(1/SUMPRODUCT(($E$2:$AI$1728)*($A$2:$A$1728=$AL2)*($B$2:$B$1728=$AM2)*($C$2:$C$1728=AP$1)*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<=DAY($AO2)))))/24,"") BA2 BA2 =1/1/(SUMPRODUCT((E2:AI17)*(A2:A17=AL2)*($B$2:$B$17=$AM2)*($C$2:$C$17=AP$1)*($E$1:$AI$1>=DAY($AN2))*(($E$1:$AI$1<=DAY($AO2)))))

Dear Etaf

Hope you all is well and i hope you got my last email

Regards

 

[etaf]

i saw the request , quite a different aproach maybe needed, but not 100% on how you receive the data and what the aim is

as you can see the limitation of using days in E to AI
but i dont know where this data comes from
if its say a month 1st march - 31st march
changing to a date - wont help goback to say beween the 28th Feb abd 3rd march as previous month will not be there

hence i'm not 100% sure what you are doing an how dates will help

if you want the sum between day1 and day 5 - that will work
But if before day1 - then its not in the file
and how far back before day 1