Formula to count if hours meet target and do not meet based

[Lukma]

Hi Friends

Please once again i am having issue around this formula in Cell W6 and X6
i have use sumifs formula to extract hours from L6 to Cell R6 upto V6 based criteria now i also need to count in Cell W6 and X6 that meet the target and do not meet based on criteria above target . which i have been help out with a formula that is useful to me , but my issue now is that it count the zero since there is a formula is all cell from R6 to V6 which i do not neet to count any Zero or Blank

Appreciate with an update solution to the formula X and Y not to count zero or blank Cell

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1																		Full	Half
2																		16	10
3	01-Jan-21
4																						Island
5	Month	Issued Month	MUS No.	Vessel	VSL	Half	Rigs & Complex	Site	Date & Time	Date & Time	Spent		Supply Vessel's	P&D VSL	Voyage Mus No	Msft Plan	Asseifiya Island	Ettouk Island	Al Ghallan Island	Umm Al Anbar	Bu Sikeen Island	Meet	Do Not meet
6	01-Jan-21			ADNOC-1010				Asseifiya Island	04/11/20 08:10	04/11/20 23:30	15		ADNOC-1010	Half			15	0	0	0	0	4	1
Sheet1

Cell Formulas
Range	Formula
L6	L6	=INT(TEXT(ABS(K6-J6),"[h]"))
R6	R6	=SUMIFS(L6:L7,I6:I7,R5,E6:E7,N6,B6:B7,B3)
S6:V6	S6	=IFERROR(SUMIFS('ILSP_Performance Tracking Data.xlsx'!Hours[#Data],'ILSP_Performance Tracking Data.xlsx'!Site[#Data],S5,'ILSP_Performance Tracking Data.xlsx'!Vessel[#Data],#REF!,'ILSP_Performance Tracking Data.xlsx'!Month[#Data],$C$5),"")
W6	W6	=IF($O6=$S$1,COUNTIFS($Q6:$V6,"<=" & $T$2),IF($O6=$T$1,COUNTIFS($Q6:$V6,"<=" & $S$2),""))
X6	X6	=IF($O6=$S$1,COUNTIF($R6:$V6,">" & $S$2),IF($O6=$T$1,COUNTIF($R6:$V6,">" & $T$2),""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5	Cell Value	contains "M"	text	NO
G5	Cell Value	contains "Naf"	text	NO

 

[maabadi]

Change formula at W6 to

=IF($O6=$S$1,COUNTIFS($Q6:$V6,"<=" & $T$2, $Q6:$V6,">" & 0),IF($O6=$T$1,COUNTIFS($Q6:$V6,"<=" & $S$2, $Q6:$V6,">" & 0),""))

AND X6 to

=IF($O6=$S$1,COUNTIF($R6:$V6,">" & $S$2, $R6:$V6,">" & 0),IF($O6=$T$1,COUNTIF($R6:$V6,">" & $T$2, $R6:$V6,">" & 0),""))

And edit formula at S6 it has #ref error for address.

 

[Lukma]

Maabadi

Thanks So much so great now i feel more relived with this am to glad my headache all gone

Appreciate