¤½¦¡Á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²¤Æ¤½¦¡

vvcvc

§Ú­n¥Î¥H¤Uªºµ²ªG¤~·|¹ïÕÙ
=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"}))
ªºµ²ªG¸ò¤U¦¡µ²ªG¤@¼Ë
=SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,"V"))

ML089

¦^´_ 12# vvcvc

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

vvcvc

¤S·Q¨ì¦pªGª¬ªp¦AÅܦ¨¦p¤U
=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"))

²z½×¤W¬O2*2­Óµ²ªG¥[Á`
¦ý­×§ï¬°¤U¦¡®É¥u¦³2­Óµ²ªG
=SUMPRODUCT(COUNTIFS(A:A,{"P","S"}, B:B,{"V","U"}))
³o¼Ë¤l¦³Åܪk¼g¦¨1­Ó¦¡¤l¶Ü?

vvcvc

¥H¬°¥[¤W""·|Åܤå¦r¤@ª½¨S¹Á¸Õ><
ÁÂÁÂML089ªº¨ó§U

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

½Æ»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

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

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

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

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