MINIFS Conditional formatting

[JARichard74]

In conditional formatting, this formula =AND(G4=MINIFS($G4:$Y4,$H$16:$Z$16,"COMPLIANT"),G4<>"") is applied to =$G$4:$G$13,$I$4:$I$13,$K$4:$K$13,$Y$4:$Y$13,$W$4:$W$13,$U$4:$U$13,$S$4:$S$13,$Q$4:$Q$13,$O$4:$O$13,$M$4:$M$13. It highlights the minimum value if the cell is not blank and H:Z is COMPLIANT. It works perfectly except for when a value in the G:Y range = 0. If I change H:Z value then the next lowest values gets highlighted as I want however, the 0 also stays highlighted. Any assistance would be appreciated.

 

[etaf]

=AND(G4=MINIFS($G4:$Y4,$H$16:$Z$16,"COMPLIANT"),G4<>"",G4<>0)

 

[jasonb75]

From what I can see the the 2 parts of the formula will be conflicting each other, try doing it without AND

=G4=MINIFS($G4:$Y4,$G4:$Y4,"<>0",$H$16:$Z$16,"COMPLIANT")

 

[JARichard74]

Wayne(etaf), your suggestion produces the same result. Jason, yours does not ignore blank cells. I also have to be careful about ignoring 0 as it is a valid response in my current context

 

[jasonb75]

Your post implied that 0 should not be highlighted, if you only want to ignore blanks then try.

=G4=MINIFS($G4:$Y4,$G4:$Y4,"<>",$H$16:$Z$16,"COMPLIANT")

 

[JARichard74]

Still highlights empty cells (See Image 1). When I move $H$16:$Z$16 to something other than COMPLIANT then none of the cells in G4:G13 should be highlighted as they should be excluded from the MIN (See image 2)

 

[etaf]

what range have you chosen ?

Book4
	G	H	I	J	K	L	M
1	FALSE	FALSE	TRUE	FALSE	FALSE
2
3
4	10	9	8
5
6
7
8
9
10
11
12
13
14
15
16		compliant		compliant		compliant
17
18
Sheet1

Cell Formulas
Range	Formula
G1:K1	G1	=AND(G4=MINIFS($G4:$Y4,$H$16:$Z$16,"COMPLIANT"),G4<>"",G4<>0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:M16	Expression	=AND(G4=MINIFS($G4:$Y4,$H$16:$Z$16,"COMPLIANT"),G4<>"",G4<>0)	text	NO

 

[jasonb75]

I was getting mixed up on the absolute and relative ranges, this should be bulletproof.

=AND(G4<>"",H$16="COMPLIANT",G4=MINIFS($G4:$Y4,$G4:$Y4,"<>",$H$16:$Z$16,"COMPLIANT"))

 

[JARichard74]

Thanks Jason, this seems to have done the trick!
Wayne, in your example I am only evaluating every second column for the MIN i.e. G,I,K,M,O,Q,S,U,W,Y