¤½¦¡ÁY´îªº°ÝÃD

vvcvc

±`±`¦³¥H¤U2ºØ±¡ªp¡A³£»Ý¦P¼Ë¤½¦¡¼g¼Æ¦¸¦A¹Bºâ
½Ð°Ý¬O§_¦³¤èªk¥i¥HÁY´î¤½¦¡
1. SUM(C:C)-SUM(D:D)+SUM(E:E)
2. SUMPRODUCT(A:A="P")+SUMPRODUCT(A:A="S")

¥H¤W±`±`¥X²{¦b¤½¦¡¤¤ªº¤@¤p¬q
ex. .....SUMPRODUCT(A:A="P")+.....SUMPRODUCT(A:A="S")   /ÂIÂIªº³¡¥÷ªí¥Ü¤½¦¡¬Û¦P
¬°¤F³o¤@¤p¬qªº®t²§±`±`¤½¦¡ªºªø«×¥Ñ3¦æÅÜ5¡B6¦æ
«áÄòºûÅ@¤]«Ü½ÆÂø
²Ä2ÂI¥»·Q¥ÎOR(SUMPRODUCT(A:A="P"),SUMPRODUCT(A:A="S"))
¦ý¨Ï¥ÎOR¤§«á´N±N°}¦CÅܦ¨«D°}¦Cªº1­Óµ²ªG

½Ð°Ý¬O§_¦³¤°»ò¤èªk¥i¥H²¤Æ¤½¦¡

hcm19522

Q1 {=SUM(MMULT(IF(C1:E999<>"",C1:E999,),{1;-1;1}))
Q2 {=COUNT(0/(FIND(A:A,0&"ABC")-1))
Q2 ¥HAÄæ=A . B .C¬°¨Ò  ;Q1¥ÎC:E ®É¶¡¸û¤[

ML089

¦^´_ 1# vvcvc

2.
=SUMPRODUCT(A:A="P")+....+SUMPRODUCT(A:A="S")

¥i¥H­×§ï¬°

=SUMPRODUCT(COUNTIF(A:A,{"P",...,"S"}))

vvcvc

mmlut¬Ý¤FEXCEL»¡©ú¤]½Æ»s½d¨Ò¹ê°µ
ÁÙ¬O·d¤£À´¥L¦bºâ¤°»ò? ºâ¥X¨Óªºµ²ªG¦³¤°»ò¥Î·N?
¥i¥H¦AÀ°¦£»¡©ú¤@¤Uexcel­pºâ¹Lµ{¶Ü?

vvcvc

¦pªGª¬ªp¬O¦h­«±ø¥ó
=SUMPRODUCT((A:A="P")*(B:B="V"))+....+SUMPRODUCT((A:A="S")**(B:B="V"))

­×§ï¬°
=SUMPRODUCT((COUNTIF(A:A,{"P",...,"S"}))*(B:B="V"))
«h¤@ª½¥X²{¿ù»~¡AµLªk¿é¤J¤½¦¡
¥i§_À°¦£¬Ý¬Ý¤½¦¡¨º¸Ì¿ù»~? ÁÂÁÂ

¦pªG¥Îªº¬Osumifs¡Bcountifs¤]¥i¥H¦pªk¬¶»s¶Ü?

ML089

¦^´_ 5# vvcvc

¦pªGª¬ªp¬O¦h­«±ø¥ó
=SUMPRODUCT((A:A="P")*(B:B="V"))+....+SUMPRODUCT((A:A="S")*(B:B="V"))

­×§ï¬°
=SUMPRODUCT(COUNTIFS(A:A,{"P", ... , "S"}, B:B,"V"))

vvcvc

¸Õ¤F¦n¤[³£Åã¥Ü¿ù»~
¥t¥~³]­p²³æªºªí³æ¸Õ¤~¦¨¥\
countifs¤]¥i¥H§ï¦¨sumifs°µ¥[Á`

(­n¦A¨Ó¬ã¨s­ì¥»­n§ïªº¤½¦¡¨ì©³¨º¸Ì¦³°ÝÃD)

vvcvc

§ä¨ì­ì¦]¬OSUMIF¡BSUMIFS¡BCOUNTIF¡BCOUNTIFS¤½¦¡ªº¼gªk¤£¯à¸Ì­±ÁÙ¦³¤½¦¡
¦pSUMIF(B:B,LEFT(A1,2),LEFT(C:C,2))
·|¦]¦¡¤l¸Ì¥]§t¤FLEFT(C:C,2)³o­Ó¤½¦¡¦Ó¿ù»~

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))

½Æ»s¥N½X

¨Ï¥Î¥H¤U²¤Æ¦¨¥\

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*"}))

½Æ»s¥N½X

¦ý¤U­±¦¡¤lSheet2!$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))

½Æ»s¥N½X

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??"}))