公式縮減的問題

vvcvc

常常有以下2種情況，都需同樣公式寫數次再運算
請問是否有方法可以縮減公式
1. SUM(C:C)-SUM(D:D)+SUM(E:E)
2. SUMPRODUCT(A:A="P")+SUMPRODUCT(A:A="S")

以上常常出現在公式中的一小段
ex. .....SUMPRODUCT(A:A="P")+.....SUMPRODUCT(A:A="S")   /點點的部份表示公式相同
為了這一小段的差異常常公式的長度由3行變5、6行
後續維護也很複雜
第2點本想用OR(SUMPRODUCT(A:A="P"),SUMPRODUCT(A:A="S"))
但使用OR之後就將陣列變成非陣列的1個結果

請問是否有什麼方法可以簡化公式

vvcvc

我要用以下的結果才會對欸
=SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,"V"))+SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,"U"))

=SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,{"V";"U"}))
的結果跟下式結果一樣
=SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,"V"))

ML089

回復 12# vvcvc

=SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,{"V";"U"}))

vvcvc

又想到如果狀況再變成如下
=SUMPRODUCT((A:A="P")*(B:B="V"))+SUMPRODUCT((A:A="S")*(B:B="V"))+SUMPRODUCT((A:A="P")*(B:B="U"))+SUMPRODUCT((A:A="S")*(B:B="U"))

理論上是2*2個結果加總
但修改為下式時只有2個結果
=SUMPRODUCT(COUNTIFS(A:A,{"P","S"}, B:B,{"V","U"}))
這樣子有變法寫成1個式子嗎?

vvcvc

以為加上""會變文字一直沒嘗試><
謝謝ML089的協助

ML089

=SUMPRODUCT(SUMIFS(
Sheet2!$P$2:$P$1488,
Sheet2!$A$2:$A$1488,E$3&"*",
Sheet2!$T$2:$T$1488,"<="&DAY($B$2+1),
Sheet2!$B$2:$B$1488,RIGHT(E$2,3),
Sheet2!$S$2:$S$1488,{"*_??","*T??"}))

vvcvc

1. =SUMPRODUCT((LEFT(Sheet2!$A$2:$A$1488,2)=LEFT(E$3,2))*(Sheet2!$T$2:$T$1488=DAY($B$2+1))*(Sheet2!$B$2:$B$1488=RIGHT(E$2,3))*(LEFT(RIGHT(Sheet2!$S$2:$S$1488,3),1)="_")*(Sheet2!$P$2:$P$1488))+SUMPRODUCT((LEFT(Sheet2!$A$2:$A$1488,2)=LEFT(E$3,2))*(Sheet2!$T$2:$T$1488=DAY($B$2+1))*(Sheet2!$B$2:$B$1488=RIGHT(E$2,3))*(LEFT(RIGHT(Sheet2!$S$2:$S$1488,3),1)="T")*(Sheet2!$P$2:$P$1488))

複製代碼

使用以下簡化成功

1. =SUMPRODUCT(SUMIFS(Sheet2!$P$2:$P$1488,Sheet2!$A$2:$A$1488,E$3&"*",Sheet2!$T$2:$T$1488,DAY($B$2+1),Sheet2!$B$2:$B$1488,RIGHT(E$2,3),Sheet2!$S$2:$S$1488,{"*)_*","*)T*"}))

複製代碼

但下面式子Sheet2!$T$2:$T$1488<=DAY($B$2+1)該怎麼修改才能簡化?

1. =SUMPRODUCT((LEFT(Sheet2!$A$2:$A$1488,2)=LEFT(E$3,2))*(Sheet2!$T$2:$T$1488<=DAY($B$2+1))*(Sheet2!$B$2:$B$1488=RIGHT(E$2,3))*(LEFT(RIGHT(Sheet2!$S$2:$S$1488,3),1)="_")*(Sheet2!$P$2:$P$1488))+SUMPRODUCT((LEFT(Sheet2!$A$2:$A$1488,2)=LEFT(E$3,2))*(Sheet2!$T$2:$T$1488<=DAY($B$2+1))*(Sheet2!$B$2:$B$1488=RIGHT(E$2,3))*(LEFT(RIGHT(Sheet2!$S$2:$S$1488,3),1)="T")*(Sheet2!$P$2:$P$1488))

複製代碼

vvcvc

找到原因是SUMIF、SUMIFS、COUNTIF、COUNTIFS公式的寫法不能裡面還有公式
如SUMIF(B:B,LEFT(A1,2),LEFT(C:C,2))
會因式子裡包含了LEFT(C:C,2)這個公式而錯誤

vvcvc

試了好久都顯示錯誤
另外設計簡單的表單試才成功
countifs也可以改成sumifs做加總

(要再來研究原本要改的公式到底那裡有問題)

ML089

回復 5# vvcvc

如果狀況是多重條件
=SUMPRODUCT((A:A="P")*(B:B="V"))+....+SUMPRODUCT((A:A="S")*(B:B="V"))

修改為
=SUMPRODUCT(COUNTIFS(A:A,{"P", ... , "S"}, B:B,"V"))