[發問] SUMPRODUCT函數+OR問提請教

KITTYBOY

DEAR  ALL大大 -
1.小弟寫一程式如下.執行無問題
=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)) 想加入一 OR  條件為如下  
   2.1 即  G1:G60000  除含有原條件  日立 字元成立外.  想新增條件  = 含有日立 或 MCL  字元均成立.
   2.2 改如下  OR(ISNUMBER(SEARCH("日立",$G$1:$G$60000)),ISNUMBER(SEARCH("MCL",$G$1:$G$60000)))
        無法抓取出統計資料
'=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.程式如何修改.煩不吝賜教   THANKS*10000

zz5151353

沒有檔案可測試,請試一試這方法

=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 大大
  您提供之函數出現BUG如下圖如附檔  煩您再不吝指導. THANKS

victorl

回復 3# KITTYBOY


SUMIF應該就可以了吧, Criteria 設定為"*日立*"(包含"日立")
=SUMIF(B2:B6,"*日立*",D2: D6)   試試看結果就是300喔

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 大大-
符合須求.THANKS *10000

DEAR  victorl  大大-
SUMIF 無法符何我多條件之需求.THANKS.

KITTYBOY

回復 6# KITTYBOY

DEAR  ALL大大 -
1.將您指導之程式使用於如附檔案.秀出 0 ????  
    1.1 原日立加總     =RANGE("B1")    10119
          但日立  或 MCL 之加總 =RANGE("B2")    0
          應 >= 10119 才對.
2.煩再不吝賜教.  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)

下載次數: 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)

下載次數: 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)