Question

求大神指点EXCEL公式

如图所示，好久之前做过了，时间长了不用忘了想不起来这样的公式怎么弄了，帮帮忙，最好解释下公式的含义

Answer 1

1. 公式为：

   =IF(OR(C2="",D2=""),"",IF(COUNTIFS(C2:D2,">=560",C2:D2,"<=840")=2,"OK","NG"))
   

2. 如图所示：

追问

不是这样的，我要的最终效果是上限下限那里随产品规格改动，而下面公式自动匹配判定，你这样的不行啊

追答

公式给你了，你自己根据自己的数据把数据加进去不就行了：
=IF(OR(C4="",D4=""),"",IF(COUNTIFS(C4:D4,">="&F$2,C4:D4,"<="&E$2)=2,"OK","NG"))

追问

有人发给我这个公式：=IF(COUNTA(C4:D4),IF(SUMPRODUCT(((C4:D4>=F2)+(C4:D4"")),"OK","NG"),"") 你看对不对，我有点看不懂这个公式是如何成立的

Answer 2

F4输入

=IF((MAX(C4:D4)<$E$2)*(MIN(C4:D4)>$F$2),"OK","NG")

公式下拉

Answer 3

=if(count(c4:d4)=0,"",if(countifs(c4:d4,">="&f$2,c4:d4,"<="&e$2)=2,"OK","NG"))
或=if(count(c4:d4)=0,"",if(and(min(c4:d4)>=f$2,max(c4:d4)<=e$2),"OK","NG"))
或=if(count(c4:d4)=0,"",if(or(min(c4:d4)<f$2,max(c4:d4)>e$2),"NG","OK"))