Countifs formula on Criteria

[Lukma]

Hi Friends

Please am Having slit trouble with the a Formula P8
i need to Count Column Q based on three Criteria
what am Trying to get is That i need to Count Column Q and with the Criteria list month Column O5, Rigs&Barges Column O6, and then last Criteria in Column if greater of equal O7 then it should count Column Q12

i hope i could get a solution to this formula

ILSP Offshore Vessel Tracking Time Spent-2022.xlsx
	K	L	M	N	O	P	Q
5					May
6					Rigs&Barges
7					70%
8					Count
9
10
11	Month	Voyage	Location	Deck %	Jackup Complex	Actual Time Spent	Exceed KPI
12	May	1179	Rig SMS Mariam	10%	Rigs&Barges	0:25
13	May	1179	Rig Al Lulu	90%	Rigs&Barges	23:45	3:45
14	May	1179	Rig Muhaiyimat		Rigs&Barges	0:20
15	May	1180	Rig Muhaiyimat	70%	Rigs&Barges	13:00
16	May	1180	Rig Al Lulu	20%	Rigs&Barges	2:05
17	May	1180	Barge Shammal	10%	Rigs&Barges	0:30
18	May	1181	Al Ghallan Island		Drl-Island	10:00	0:00
19	May	1181	Rig SMS Faith	0%	Rigs&Barges	0:05
20	May	1181	Rig Al Yasat	90%	Rigs&Barges	26:50	6:50
21	May	1182	Rig SMS Esse	40%	Rigs&Barges	19:45	5:45
22	May	1182	Rig Yemillah	10%	Rigs&Barges	1:45
23	May	1182	Rig Al Hudiariyat	30%	Rigs&Barges	4:50
24	May	1183	Rig SMS Faith	95%	Rigs&Barges	34:40	14:40
25	May	1183	Al Ghallan Island		Drl-Island	7:24
26	May	1183	Rig Al Bzoom		Rigs&Barges	0:35
27	May	1183	Rig Al Ittihad		Rigs&Barges	0:57
28	May	1183	Rig Ariabahatt-1		Rigs&Barges	4:22
29	May	1184	Rig Al Reem		Rigs&Barges	42:40	22:40
30	May	1184	Al Qatia Island		Drl-Island	0:30
31	May	1185	Zirku Island	100%	Prd-Island	4:40
32	May	1185	Ettouk Island		Drl-Island	1:35
33	May	1185	Allianz Power			3:10
34	May	1185	Bu Sikeen Island		Drl-Island	0:35
35	May	1186	Rig Al Yasat	60%	Rigs&Barges	16:10	2:10
36	May	1186	Rig Al Bzoom	10%	Rigs&Barges	2:30
37	May	1186	Rig SMS Faith	30%	Rigs&Barges	14:00	0:00
38	May	1187	Bu Sikeen Island	20%	Drl-Island	45:05	35:05
39	May	1187	Al Qatia Island	70%	Drl-Island	10:58	0:58
ILSP_Offshore_Vessel_Tracking

Cell Formulas
Range	Formula
K12:N1211	K12	=UNIQUE(CHOOSE({1,2,3,4},B12:B1864,C12:C1864,E12:E1864,F12:F1864))
O12:O39	O12	=IFERROR(VLOOKUP(M12,Location,2,0),"")
P12:P39	P12	=IFERROR(1/(1/SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)),"")
Q12:Q39	Q12	=IF(AND(N12>=$C$4,O12=$A$4,P12>$C$10),P12-$C$10,IF(AND(N12<=$C$5,O12=$A$5,P12>$D$10),P12-$D$10,IF(AND(O12="",O12=$A$4,P12>$D$10),P12-$D$10,IF(AND(O12=$A$6,P12>$E$10),P12-$E$10,IF(AND(O12=$A$7,P12>$F$10),P12-$F$10,IF(AND(O12=$A$8,P12>$F$10),P12-$F$10,""))))))
Dynamic array formulas.

 

[RoryA]

Are the values in column B actual dates, or just month names? If they are dates, are they scattered throughout a month, or are they the first of the month like O5 is?

 

[Lukma]

HI Rorya

Thanks for you feedback please here is the my data i have tried some formula which am not still sure if am getting it alright

Column D4 i need to Count Column Q based on Criteria
Colum E4 need to sum Column Q based on Criteria
Column F4 i need to Fine average Column Q based on Criteria but am unable to get the formula yet for that

Appreciate your support to look into my formula
Thanks

ILSP Offshore Vessel Tracking Time Spent-2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3		ILSP	KPI	No's of Visit	Total Hours	Average hrs.
4	Rigs&Barges	JackRigs	70%	13	332:46	25:35
5	Rigs&Barges	JackRigs	69%	28	220:43	7:52
6	Complex	Complex	12Hrs	12	87:08	7:15
7	Drl-Island	Drl-Island	10 Hrs	12	68:11	5:40
8	Prd-Island	Prd-Island	10 Hrs	4	3:00	0:45
9		June	70%	69%
10		ILSP KPI	20	14	12	10
11		Month	Voyage	Vessel	Location	Deck Load %	Location Start Date & Time	Location End Date & Time	Total Time		Month	Voyage	Location	Deck %	Jackup Complex	Actual Time Spent	Exceed KPI
12		April	1044	Z-QUEEN	ABK Complex	80%	27/04/22 07:10	27/04/22 08:02	0:52		April	1044	ABK Complex	80%	Complex	26:46	14:46
13		April	1044	Z-QUEEN	ABK Complex	80%	27/04/22 09:40	27/04/22 09:48	0:08		May	1044	ABK Complex	80%	Complex	26:46	14:46
14		April	1044	Z-QUEEN	ABK Complex	80%	28/04/22 07:10	28/04/22 07:52	0:42		May	1044	Mutawa-202			0:15
15		April	1044	Z-QUEEN	ABK Complex	80%	28/04/22 08:15	28/04/22 08:23	0:08		April	1045	Rig Vivekanand-3	100%	Rigs&Barges	63:00	43:00
16		May	1044	Z-QUEEN	ABK Complex	80%	01/05/22 06:50	01/05/22 07:07	0:17		April	1045	Rig Yemillah		Rigs&Barges	12:10
17		May	1044	Z-QUEEN	ABK Complex	80%	01/05/22 08:25	02/05/22 08:33	24:08		April	1046	Nasr Complex		Complex	10:15
18		May	1044	Z-QUEEN	Mutawa-202		01/05/22 14:30	01/05/22 14:45	0:15		April	1046	Barge Pepper	20%	Prd-Barge	2:30
19		May	1044	Z-QUEEN	ABK Complex	80%	02/05/22 08:55	02/05/22 09:14	0:19		April	1047	Al Qatia Island	50%	Drl-Island	2:34
20		May	1044	Z-QUEEN	ABK Complex	80%	02/05/22 10:40	02/05/22 10:52	0:12		April	1047	Bu Sikeen Island	20%	Drl-Island	0:33
21		April	1045	QMS MARIMBA	Rig Vivekanand-3	100%	27/04/22 15:30	27/04/22 20:10	4:40		April	1048	Al Ghallan Island	10%	Drl-Island	0:20
22		April	1045	QMS MARIMBA	Rig Yemillah		27/04/22 22:20	28/04/22 10:30	12:10		April	1048	Umm Al Anbar	30%	Drl-Island	10:50	0:50
23		April	1045	QMS MARIMBA	Rig Vivekanand-3	100%	28/04/22 12:00	30/04/22 22:20	58:20		April	1048	Asseifiya Island	10%	Drl-Island	5:30
24		April	1046	ADNOC-810	Nasr Complex		27/04/22 08:00	27/04/22 18:15	10:15		April	1048	Ettouk Island	60%	Drl-Island	8:35
25		April	1046	ADNOC-810	Barge Pepper	20%	27/04/22 21:30	28/04/22 00:00	2:30		April	1049	Rig Junana		Rigs&Barges	0:05
26		April	1047	LCT-WARDEH	Al Qatia Island	50%	28/04/22 02:00	28/04/22 02:40	0:40		April	1049	Rig SMS Esse		Rigs&Barges	19:15
27		April	1047	LCT-WARDEH	Al Qatia Island	50%	28/04/22 03:00	28/04/22 04:48	1:48		April	1049	Rig Yemillah	40%	Rigs&Barges	6:10
28		April	1047	LCT-WARDEH	Bu Sikeen Island	20%	28/04/22 06:36	28/04/22 06:54	0:18		April	1050	Rig Butinah	10%	Rigs&Barges	4:37
29		April	1047	LCT-WARDEH	Bu Sikeen Island	20%	28/04/22 08:30	28/04/22 08:45	0:15		April	1050	Rig Al Shuweihat	20%	Rigs&Barges	7:40
30		April	1047	LCT-WARDEH	Al Qatia Island	50%	28/04/22 10:24	28/04/22 10:30	0:06		May	1050	Al Ghallan Island	60%	Drl-Island	4:35
31		April	1048	ADNOC-224	Al Ghallan Island	10%	27/04/22 08:10	27/04/22 08:30	0:20		May	1050	Rig Butinah	10%	Rigs&Barges	4:37
32		April	1048	ADNOC-224	Umm Al Anbar	30%	27/04/22 10:55	27/04/22 21:45	10:50		May	1050	Rig Junana		Rigs&Barges	6:10
33		April	1048	ADNOC-224	Asseifiya Island	10%	28/04/22 01:45	28/04/22 07:15	5:30		April	1051	Zirku Island	60%	Prd-Island	8:10
34		April	1048	ADNOC-224	Ettouk Island	60%	28/04/22 09:50	28/04/22 18:25	8:35		April	1051	Barge Pili-Pili	40%	Prd-Barge	3:20
35		April	1049	AMS-RUBY	Rig Junana		27/04/22 19:45	27/04/22 19:50	0:05		April	1052	Das Island			8:44
ILSP_Offshore_Vessel_Tracking

Cell Formulas
Range	Formula
D4	D4	=COUNTIFS($Q$12:$Q$900000,">0:0",$O$12:$O$900000,$A$5,$K$12:$K$900000,$B$9,$N$12:$N$900000,">=70%")
E4	E4	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9, $N$12:$N$900000,">=70%")
F4:F8	F4	=E4/D4
D5	D5	=COUNTIFS($Q$12:$Q$900000,">0:0",$O$12:$O$900000,$A$5,$K$12:$K$900000,$B$9,$N$12:$N$900000,"<=69%")
E5	E5	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$A$4,$K$12:$K$900000,$B$9, $N$12:$N$900000,"<=69%")
D6	D6	=COUNTIFS($O$12:$O$900000,$B$6,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E6	E6	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$6,$K$12:$K$900000,$B$9)
D7	D7	=COUNTIFS($O$12:$O$900000,$B$7,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E7	E7	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$7,$K$12:$K$900000,$B$9)
D8	D8	=COUNTIFS($O$12:$O$900000,$B$8,$K$12:$K$900000,$B$9,$Q$12:$Q$900000,">0")
E8	E8	=SUMIFS($Q$12:$Q$900000,$O$12:$O$900000,$B$8,$K$12:$K$900000,$B$9)
K12:N1626	K12	=UNIQUE(CHOOSE({1,2,3,4},B12:B2410,C12:C2410,E12:E2410,F12:F2410))
O12:O35	O12	=IFERROR(VLOOKUP(M12,Location,2,0),"")
P12:P35	P12	=IFERROR(1/(1/SUMIFS(Vessel_Tracking[Total Time],Vessel_Tracking[Location],M12,Vessel_Tracking[Voyage],L12)),"")
Q12:Q35	Q12	=IF(AND(N12>=$C$4,O12=$A$4,P12>$C$10),P12-$C$10,IF(AND(N12<=$C$5,O12=$A$5,P12>$D$10),P12-$D$10,IF(AND(O12="",O12=$A$4,P12>$D$10),P12-$D$10,IF(AND(O12=$A$6,P12>$E$10),P12-$E$10,IF(AND(O12=$A$7,P12>$F$10),P12-$F$10,IF(AND(O12=$A$8,P12>$F$10),P12-$F$10,""))))))
B12:B35	B12	=IFERROR(EOMONTH([@[Location Start Date & Time ]],-1)+1,"")
I12:I35	I12	=IFERROR(1/(1/SUM([@[Location End Date & Time ]]-[@[Location Start Date & Time ]])),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B9	List	=Info_Setting!$K$3:$K$15