close
excel SUMPRODUCT 跨欄統計問題
=SUMPRODUCT(($C$2:$C$3000="FB")*ISNUMBER(0/MMULT(1-ISERR(FIND({"80格","FB-77KB601","製造機"},$E$2:$E$3000)),{1;1;1}))*($F$2:$F$3000="製造機"))+SUMPRODUCT(($C$2:$C$3000="FB")*ISNUMBER(0/MMULT(1-ISERR(FIND({"80格","FB-77KB601"},$E$2:$E$3000)),{1;1...顯示更多=SUMPRODUCT(($C$2:$C$3000="FB")*ISNUMBER(0/MMULT(1-ISERR(FIND({"80格","FB-77KB601","製造機"},$E$2:$E$3000)),{1;1;1}))*($F$2:$F$3000="製造機"))+SUMPRODUCT(($C$2:$C$3000="FB")*ISNUMBER(0/MMULT(1-ISERR(FIND({"80格","FB-77KB601"},$E$2:$E$3000)),{1;1}))*ISNUMBER(FIND("製造機",$E$2:$E$3000)))以上公式需加總條件為：條件1：$C$2:$C$3000="FB"條件2：$E$2:$E$3000符合"80格"或"FB-77KB601"或"製造機"條件3：$F$2:$F$3000="製造機"或條件1：$C$2:$C$3000="FB"條件2：$E$2:$E$3000符合"80格"或"FB-77KB601"條件3：$E$2:$E$3000符合"製造機"請教如何修正或縮短以上公式，希望當如果$E$2:$E$3000符合"80格"或"FB-77KB601"時，且$E$2:$F$3000任一符合"製造機"均加總為1。但如果$E$2:$F$3000同時符合"製造機"仍加總為1。求解~~~~更新:抱歉，忘記補充，另想請教如果需統計條件欲排除"製造機"又該如何設定，謝謝。例如：條件1：$C$2:$C$3000="FB"條件2：$E$2:$E$3000符合"80格"或"FB-77KB601"條件3：$F$2:$F$3000<>"製造機"或條件1：$C$2:$C$3000="FB"條件2：$E$2:$E$3000符合"80格"或"FB-77KB601"條件3：$E$2:$E$3000不可符合"製造機"更新2:c大~~~非常感謝，但001、002公式測試好像不會成功耶！如果不麻煩的話再麻煩協助一下了，再次感謝~更新3:c大~昨天就有寄測試檔給您了說，已經有再次補寄了！非常感謝幫忙喔！更新4:c大~非常感謝，測試成功，可否麻煩轉貼回答，再次感謝！
借用准大公式,試看看! =SUMPRODUCT(ISNUMBER(0/(FIND($C$2:$C$3000,"_FB")-1)/(FIND($E$2:$E$3000,"_80格FB-77KB601")-1)/(FIND($E$2:$E$3000,"_製造機")-1))*1) 2015-03-13 16:12:13 補充： 承上 =SUMPRODUCT(ISNUMBER(0/(FIND($C$2:$C$3000,"_FB")-1)/(FIND($E$2:$E$3000,"_80格FB-77KB601")-1)/MMULT(N($E$2:$F$3000="_製造機"),{1;1}))*1) 2015-03-13 16:12:19 補充： =SUMPRODUCT(ISNUMBER(0/(FIND($C$2:$C$3000,"_FB")-1)/(FIND($E$2:$E$3000,"_80格FB-77KB601")-1))*ISERR(0/(FIND($E$2:$E$3000,"_製造機")-1))) 2015-03-14 11:56:38 補充： To版大 可否提供測試檔？ 2015-03-14 23:51:40 補充： 第一式改成 =SUMPRODUCT(ISNUMBER(0/(FIND($C$2:$C$3000,"_FB")-1)/(FIND($D$2:$D$3000,"_80格製造機FB-77KB601")-1)/(FIND($E$2:$E$3000,"_製造機")-1))*1) 2015-03-14 23:54:31 補充： 另外002式不太了解問題的意思,所以用了兩種公式試看看? =SUMPRODUCT(ISNUMBER(0/(FIND($C$2:$C$3000,"_FB")-1)/(FIND($D$2:$D$3000,"_80格製造機FB-77KB601")-1))*1) 2015-03-14 23:55:03 補充： =SUMPRODUCT(ISNUMBER(0/(FIND($C$2:$C$3000,"_FB")-1)/(FIND($D$2:$D$3000,"_80格製造機FB-77KB601")-1))*ISERR(MMULT(0/(FIND($D$2:$E$3000,"_製造機")-{1,1}),{1;1}))) 2015-03-16 09:43:07 補充： 借用准大公式 =SUMPRODUCT(ISNUMBER(0/(FIND($C$2:$C$3000,"_FB")-1)/(FIND($D$2:$D$3000,"_80格製造機FB-77KB601")-1)/(FIND($E$2:$E$3000,"_製造機")-1))*1) =SUMPRODUCT(ISNUMBER(0/(FIND($C$2:$C$3000,"_FB")-1)/(FIND($D$2:$D$3000,"_80格製造機FB-77KB601")-1))*ISERR(MMULT(0/(FIND($D$2:$E$3000,"_製造機")-{1,1}),{1;1}))) =SUMPRODUCT(ISNUMBER(0/(FIND($C$2:$C$3000,"_FB")-1)/(FIND($E$2:$E$3000,"_80格FB-77KB601")-1))*ISERR(0/(FIND($E$2:$E$3000,"_製造機")-1)))
條件一：(C$2:C$3000="FB")條件二：(MMULT(1-ISERR(FIND({"80格","FB-77KB601"},E$2:E$3000)),{1;1})>0)條件三：ISNUMBER(FIND("製造機",E$2:E$3000&F$2:F$3000))沒檔案純猜測供參考，以Ｃ大為主～～
以上文章來自奇摩知識家，如有侵犯請留言告知
https://tw.answers.yahoo.com/question/index?qid=20150313000016KK01244