[µo°Ý] SUMPRODUCT¨ç¼Æ+OR°Ý´£½Ð±Ð

KITTYBOY

DEAR  ALL¤j¤j -
1.¤p§Ì¼g¤@µ{¦¡¦p¤U.°õ¦æµL°ÝÃD
=SUMPRODUCT(ISNUMBER(SEARCH("CC",$F$1:$F$60000))*ISNUMBER(SEARCH("¤é¥ß",$G$1:$G$60000))*ISNUMBER(SEARCH("C2",$E$1:$E$60000)),$R$1:$R$60000)
2.¦ý­ì±ø¥ó ISNUMBER(SEARCH("¤é¥ß",$G$1:$G$60000)) ·Q¥[¤J¤@ OR  ±ø¥ó¬°¦p¤U  
   2.1 §Y  G1:G60000  °£§t¦³­ì±ø¥ó  ¤é¥ß ¦r¤¸¦¨¥ß¥~.  ·Q·s¼W±ø¥ó  = §t¦³¤é¥ß ©Î MCL  ¦r¤¸§¡¦¨¥ß.
   2.2 §ï¦p¤U  OR(ISNUMBER(SEARCH("¤é¥ß",$G$1:$G$60000)),ISNUMBER(SEARCH("MCL",$G$1:$G$60000)))
        µLªk§ì¨ú¥X²Î­p¸ê®Æ
'=SUMPRODUCT(ISNUMBER(SEARCH("CC",$F$1:$F$60000))*OR(ISNUMBER(SEARCH("¤é¥ß",$G$1:$G$60000)),ISNUMBER(SEARCH("MCL",$G$1:$G$60000)))*ISNUMBER(SEARCH("C2",$E$1:$E$60000)),$R$1:$R$60000)
3.µ{¦¡¦p¦ó­×§ï.·Ð¤£§[½ç±Ð   THANKS*10000

zz5151353

¨S¦³ÀÉ®×¥i´ú¸Õ,½Ð¸Õ¤@¸Õ³o¤èªk

=SUMPRODUCT(ISNUMBER(SEARCH("CC",$F$1:$F$60000))*ISNUMBER(SEARCH({"¤é¥ß","MCL"},$G$1:$G$60000))*ISNUMBER(SEARCH("C2",$E$1:$E$60000))*$R$1:$R$60000)

KITTYBOY

¦^´_ 2# zz5151353

DERA ¤j¤j
  ±z´£¨Ñ¤§¨ç¼Æ¥X²{BUG¦p¤U¹Ï¦pªþÀÉ  ·Ð±z¦A¤£§[«ü¾É. THANKS

victorl

¦^´_ 3# KITTYBOY


SUMIFÀ³¸Ó´N¥i¥H¤F§a, Criteria ³]©w¬°"*¤é¥ß*"(¥]§t"¤é¥ß")
=SUMIF(B2:B6,"*¤é¥ß*",D2: D6)   ¸Õ¸Õ¬Ýµ²ªG´N¬O300³á

zz5151353

E8

=SUMPRODUCT(ISNUMBER(SEARCH("CC",$A$1:$A$60000))*(OR(ISNUMBER(SEARCH($B$1:$B$60000,{"¤é¥ß";"MCL"}))))*ISNUMBER(SEARCH("C2",$C$1:$C$60000)),$D$1:$D$60000)

KITTYBOY

DEAR  zz5151353 ¤j¤j-
²Å¦X¶·¨D.THANKS *10000

DEAR  victorl  ¤j¤j-
SUMIF µLªk²Å¦ó§Ú¦h±ø¥ó¤§»Ý¨D.THANKS.

KITTYBOY

¦^´_ 6# KITTYBOY

DEAR  ALL¤j¤j -
1.±N±z«ü¾É¤§µ{¦¡¨Ï¥Î©ó¦pªþÀÉ®×.¨q¥X 0 ????  
    1.1 ­ì¤é¥ß¥[Á`     =RANGE("B1")    10119
          ¦ý¤é¥ß  ©Î MCL ¤§¥[Á` =RANGE("B2")    0
          À³ >= 10119 ¤~¹ï.
2.·Ð¦A¤£§[½ç±Ð.  THANKS

Hsieh

{=SUM(IF((ISNUMBER(FIND("CC",$F$2:$F$6000)))*((ISNUMBER(FIND("¤é¥ß",$G$2:$G$6000)))+(ISNUMBER(FIND("MCL",$G$2:$G$6000)))),$R$2:$R$6000,))} 1.rar (55.33 KB)

¤U¸ü¦¸¼Æ: 13

2010-7-2 19:12

PD961A

¦^´_ 7# KITTYBOY


     '=SUMPRODUCT(ISNUMBER(SEARCH("CC",$F$1:$F$60000))*(OR(ISNUMBER(SEARCH($G$1:$G$60000,{"¤é¥ß","MCL"}))))*ISNUMBER(SEARCH("C2",$E$1:$E$60000)),$R$1:$R$60000)

1-1.rar (55.03 KB)

¤U¸ü¦¸¼Æ: 12

2010-7-2 19:33

¸Õ¸Õ¬Ý...

zz5151353

B1

=SUMPRODUCT(ISNUMBER(SEARCH("C2",$E$1:$E$6000))*ISNUMBER(SEARCH("CC",$F$1:$F$6000))*ISNUMBER(SEARCH($G$1:$G$6000,"¤é¥ß"))+ISNUMBER(SEARCH($G$1:$G$6000,"MCL")),$R$1:$R$6000)