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

 

[shift-del]

There are things I don't understand.

From Column D to Colum AH i have date numbers

I can see 15.4 as April 15th. But there is no year. Further to the right is 15. What kind of date number would that be without month and year?

So i need to sumifs based on multiple criteria between dates

Not clear what you mean by "multiple criteria".

So let say from Column AM date 2 and AN 7

Neither 2 nor 7 are in these columns. Because they are no dates.

hen i need to sum Column D date 2 to 7 all time based on Criteria

Column D has no data in it.

and i hope am able to explain better

Please do explain better. And show the expected outcome.

 

[etaf]

does this work for you
=SUMPRODUCT(($D$2:$AH$20)*($A$2:$A$20=$AL2)*($B$2:$B$20=AO$1)*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))

i'm assuming just looking at the day() and the month or year does not matter - that will depend a lot on how you structure the data

Expected results are always good to show in a sample - so any formula provided can be tested against the results you expect

Book1
	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.85416667		A-CHLOE	1/2/23 6:20	1/7/23 1:20	1.6	0	15.4	18.7	0	0	0	0	0	0
3	A-CHLOE	Waiting COP MOSB	Waitng ADNOC Berthing Instruction																																0	0		A-CHLOE	1/7/23 1:20	1/11/23 18:30	0	0	20.3	11.4	0	0	0	0	0	0
4	A-CHLOE	Waiting COP MOSB	Waiting Base Cargo Operation																																0	0		A-CHLOE	1/11/23 18:30	1/19/23 1:10	12.3	0	32	40.6	0	0	0	0	0	0
5	A-CHLOE	Waiting ADOF Supplier	Waitng Drilling Material																																0	0		A-CHLOE	1/19/23 1:10	1/25/23 8:50	0	0	14.4	11.3	0	0	0	0	0	0
6	A-CHLOE	Waiting COP MOSB	Cargo Operation Suspended																																0	0		A-CHLOE	1/25/23 8:50	2/2/23 7:00	0	0	0	0	0	0	0	0	0	0
7	A-CHLOE	Waiting VTS	Waiting VTS permission		1.6												4.3			8															13.9	0.57916667		A-GRACE	1/1/23 10:20	1/6/23 6:50	0	0	0	0	0	0	0	0	0	0
8	A-CHLOE	Waiting VTS	Waiting on Channel Closure																																0	0		A-GRACE	1/6/23 6:50	1/10/23 1:25	0	0	0	0	0	0	0	0	0	0
9	A-CHLOE	Waiting Weather	Waitinhg WOW , FOG / POOR VISIBILITY																																0	0		A-GRACE	1/10/23 1:25	1/19/23 8:00	0	0	0	0	0	0	0	0	0	0
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.33333333		A-GRACE	1/19/23 8:00	1/25/23 1:30	0	0	0	0	0	0	0	0	0	0
11	A-CHLOE	Working Free-Port	Free-Port / Mus-Port																																0	0		A-GRACE	1/25/23 1:30	1/31/23 18:25	0	0	0	0	0	0	0	0	0	0
12	A-CHLOE	Waiting COP Free-Port	Standby location ( Waiting )																																0	0		A-GRACE	1/31/23 18:25	2/3/23 23:10	0	0	0	0	0	0	0	0	0	0
13
Sheet1

Cell Formulas
Range	Formula
AI2:AI12	AI2	=SUM(D2:AH2)
AJ2:AJ12	AJ2	=AI2/24
AO2:AX12	AO2	=SUMPRODUCT(($D$2:$AH$20)*($A$2:$A$20=$AL2)*($B$2:$B$20=AO$1)*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))

 

[Lukma]

Dear etaf

many thanks it's super great, just exactly what i need to get my work faster and easy i was so worried thinking of this all night.

my concern was raised by Shift-del, which i understand that the date from column D to AH is just number, but am super happy that etaf understand it all.

now i have to increase the range to complete my report today

Am also using this to say thanks to everyone out there.

Regards

does this work for you
=SUMPRODUCT(($D$2:$AH$20)*($A$2:$A$20=$AL2)*($B$2:$B$20=AO$1)*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))

i'm assuming just looking at the day() and the month or year does not matter - that will depend a lot on how you structure the data

Expected results are always good to show in a sample - so any formula provided can be tested against the results you expect

Book1
	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.85416667		A-CHLOE	1/2/23 6:20	1/7/23 1:20	1.6	0	15.4	18.7	0	0	0	0	0	0
3	A-CHLOE	Waiting COP MOSB	Waitng ADNOC Berthing Instruction																																0	0		A-CHLOE	1/7/23 1:20	1/11/23 18:30	0	0	20.3	11.4	0	0	0	0	0	0
4	A-CHLOE	Waiting COP MOSB	Waiting Base Cargo Operation																																0	0		A-CHLOE	1/11/23 18:30	1/19/23 1:10	12.3	0	32	40.6	0	0	0	0	0	0
5	A-CHLOE	Waiting ADOF Supplier	Waitng Drilling Material																																0	0		A-CHLOE	1/19/23 1:10	1/25/23 8:50	0	0	14.4	11.3	0	0	0	0	0	0
6	A-CHLOE	Waiting COP MOSB	Cargo Operation Suspended																																0	0		A-CHLOE	1/25/23 8:50	2/2/23 7:00	0	0	0	0	0	0	0	0	0	0
7	A-CHLOE	Waiting VTS	Waiting VTS permission		1.6												4.3			8															13.9	0.57916667		A-GRACE	1/1/23 10:20	1/6/23 6:50	0	0	0	0	0	0	0	0	0	0
8	A-CHLOE	Waiting VTS	Waiting on Channel Closure																																0	0		A-GRACE	1/6/23 6:50	1/10/23 1:25	0	0	0	0	0	0	0	0	0	0
9	A-CHLOE	Waiting Weather	Waitinhg WOW , FOG / POOR VISIBILITY																																0	0		A-GRACE	1/10/23 1:25	1/19/23 8:00	0	0	0	0	0	0	0	0	0	0
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.33333333		A-GRACE	1/19/23 8:00	1/25/23 1:30	0	0	0	0	0	0	0	0	0	0
11	A-CHLOE	Working Free-Port	Free-Port / Mus-Port																																0	0		A-GRACE	1/25/23 1:30	1/31/23 18:25	0	0	0	0	0	0	0	0	0	0
12	A-CHLOE	Waiting COP Free-Port	Standby location ( Waiting )																																0	0		A-GRACE	1/31/23 18:25	2/3/23 23:10	0	0	0	0	0	0	0	0	0	0
13
Sheet1

Cell Formulas
Range	Formula
AI2:AI12	AI2	=SUM(D2:AH2)
AJ2:AJ12	AJ2	=AI2/24
AO2:AX12	AO2	=SUMPRODUCT(($D$2:$AH$20)*($A$2:$A$20=$AL2)*($B$2:$B$20=AO$1)*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))

 

[etaf]

I may have NOT provided what you want - so please check the results are exactly as needed

which i understand that the date from column D to AH is just number,

yes dates are numbers , based on 0/1/1900 and each day increments by 1
22nd March 24 is the number 45373
23rd March 24 will be 45374
so incremented by 1


you only have 1 - 31

in column D to AH is just number,

and hence i thought you wanted to sum based on what the day of the month was - 1st,2nd,3rd,4th etc

BUT , on reflection , THIS MAY NOT BE doing what you WANT
As Shift-del, has said, NO Dates in D to AH just numbers

and so it is adding up based on the day of the month

1st jan 24 to 1st feb 24 - will only look in the column 1 - it will NOT add up from column 1 to 31 - as that is a full month which is 1-jan to 1-feb
Just the days starting the 1st - regardless of the dates

Hence why i say

i'm assuming just looking at the day() and the month or year does not matter - that will depend a lot on how you structure the data

Expected results are always good to show in a sample - so any formula provided can be tested against the results you expect

and
DAY(date cell) will just get the day of the month
hence
*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))
and that the data is looking at the day of the start and stop dates

 

[Lukma]

I may have NOT provided what you want - so please check the results are exactly as needed



yes dates are numbers , based on 0/1/1900 and each day increments by 1
22nd March 24 is the number 45373
23rd March 24 will be 45374
so incremented by 1


you only have 1 - 31

and hence i thought you wanted to sum based on what the day of the month was - 1st,2nd,3rd,4th etc

BUT , on reflection , THIS MAY NOT BE doing what you WANT
As Shift-del, has said, NO Dates in D to AH just numbers

and so it is adding up based on the day of the month

1st jan 24 to 1st feb 24 - will only look in the column 1 - it will NOT add up from column 1 to 31 - as that is a full month which is 1-jan to 1-feb
Just the days starting the 1st - regardless of the dates

Hence why i say


and
DAY(date cell) will just get the day of the month
hence
*($D$1:$AH$1>=DAY($AM2))*(($D$1:$AH$1<=DAY($AN2))))
and that the data is looking at the day of the start and stop dates

Good Day Etaf

based on your comment I also see that is not picking the last days of the month however can you look into this since I have some days in the month for email
January 2023 is not picking any entry

can you into the formula if something can be updated for me

However Please sorry for my late respond was off for a while

Thanks

Working.xlsx
	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
1	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	LCT ADNOC-1010	01/01/23 22:50	05/01/23 22:30	5.9	5.6	11.7	61.3		20.3		9.5		5.7
3	LCT ADNOC-1010	05/01/23 22:30	09/01/23 06:50		13.9	20.9	57.2		16.6		5.3		6.1
4	LCT ADNOC-1010	09/01/23 06:50	14/01/23 10:20		9.9	17.9	46.3		11.3		35.2		23.4
5	LCT ADNOC-1010	14/01/23 10:20	17/01/23 20:40		13.2	7.9	45.3		12.5		5.5		11.6
6	LCT ADNOC-1010	17/01/23 20:40	21/01/23 21:55		15	15.2	48.8		10.4		3.6		27
7	LCT ADNOC-1010	21/01/23 21:55	25/01/23 01:35		32.2	17.3	45.8		14.9		4		5.8
8	LCT ADNOC-1010	25/01/23 01:35	31/01/23 10:20		22.7	5.4	28.9		6.8		7.1		97.1
9	LCT ADNOC-1010	31/01/23 10:20	04/02/23 02:05
Sheet1

Cell Formulas
Range	Formula
AP2:AZ9	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))))),"")

 

[etaf]

that is not all the sheet - can you post the sheet and also show where its not working
just want to make sure i look at the real data and cells

 

[Lukma]

Dear Etaf

Many thanks for your response, in addition, I also tried to extend the range due to different Vessel names in Column B but unfortunately, I could not result
to explain in detail I have compiled all month's reports in one sheet then I have a list of each month in column A which means am to see the next entry of the vessel report for the following month till December.. after that, I decide to extend the range criteria but each time I tried no result is found, trying to find an easy way I could get the job done fast for all vessel each month.

However, i also need to ask a few questions is there a way to post the full datasheet as am using xl2bb to post mini sheet

Thank you once more

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.25	0.23	0.49	2.55		0.85		0.40		0.24
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.58	0.87	2.38		0.69		0.22		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.41	0.75	1.93		0.47		1.47		0.98
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.55	0.33	1.89		0.52		0.23		0.48
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.63	0.63	2.03		0.43		0.15		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		1.34	0.72	1.91		0.62		0.17		0.24
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.95	0.23	1.20		0.28		0.30		4.05
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
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		1.52	0.66	1.38		0.35		0.10
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		1.04	0.71	1.30		0.45		0.18		2.32
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		0.74	0.34	1.75		0.57		0.60
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		2.68	0.43	2.36		0.47		0.27		2.80
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		1.42	0.88	2.55		0.92		0.24
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		1.44	0.40	1.62		0.36		0.19
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		2.86	0.98	1.58		0.48		0.11
Sheet1

Cell Formulas
Range	Formula
AJ2:AJ17	AJ2	=SUM(E2:AI2)
AK2:AK17	AK2	=AJ2/24
AL2:AL17	AL2	=IFERROR(EOMONTH(AN2,-1)+1,"")
AP2:AZ17	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,"")

 

[etaf]

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[etaf]

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)))))