ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¥»©«³Ì«á¥Ñ zz5151353 ©ó 2010-7-1 17:15 ½s¿è

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 ©ó 2010-7-1 17:35 ½s¿è

¨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)
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 2# zz5151353

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

2.JPG (59.08 KB)

2.JPG

2.JPG (59.08 KB)

2.JPG

1.rar (1.68 KB)

1.rar (1.68 KB)

TOP

¦^´_ 3# KITTYBOY


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

TOP

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)
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

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

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

TOP

¦^´_ 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

2.JPG (47.98 KB)

2.JPG

1.rar (56.07 KB)

TOP

{=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)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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)

¸Õ¸Õ¬Ý...

TOP

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)
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD