[µo°Ý] sumif ¤½¦¡¦p¦ó­×§ï?

t8899

=SUMIF(H3:H301,"v",F3:F301)
·Q§ï¦¨F3:F301³Ì«á30µ§(¦³¸ê®Æ)ªºÁ`¦X??(³Ì«á¤@µ§¦³¸ê®Æ¡A©¹«e¼Æ30­ÓÀx¦s®æ)

p212

¦^´_ 1# t8899
F3:F301¸ê®Æ¬°³sÄò©Êªº¡A§í¬°¤£³sÄò¡H°ÝÃD¦³ÂI¼Ò½k¡B¦³³\¦hªº¥i¯à¡A¥i§_´£¨Ñ½d¨ÒÀɰѦҬã¨s¡H
ÁÂÁ¡I

t8899

¦^´_  t8899
F3:F301¸ê®Æ¬°³sÄò©Êªº¡A§í¬°¤£³sÄò¡H°ÝÃD¦³ÂI¼Ò½k¡B¦³³\¦hªº¥i¯à¡A¥i§_´£¨Ñ½d¨ÒÀɰѦҬã¨s¡H ...
p212 µoªí©ó 2014-4-15 13:50

Book3.rar (9.52 KB)

¤U¸ü¦¸¼Æ: 13

2014-4-15 16:15

³sÄò©Ê(Àɮתº¨Ò¤lF19:F48),¦p¦A¦h¤@¦æÅܦ¨ F20:F49
¡A¤£³sÄòÃø«×À³¸Ó«Ü°ª

p212

¦^´_ 3# t8899
1¡B½Ð©óBook3.xls¥ý¶i¦æ¡u©w¸q¦WºÙ¡v (±ø¥ó¡G­pºâ¸ê®Æ¸s³Ì«á¤@µ§¦³¸ê®ÆªºÀx¦s®æ©¹«e¼Æ30­ÓÀx¦s®æ)
x =Sheet1!$H$3:$H$301
y =OFFSET(Sheet1!$D$1,MAX(IF(x="v",ROW(x),FALSE))-30,4,30)
z =OFFSET(Sheet1!$D$1,MAX(IF(x="v",ROW(x),FALSE))-30,2,30)
2¡B±N1#°Ýªº¤½¦¡=SUMIF(H3:H301,"v",F3:F301)§ï³y¦¨
=SUMIF(y,"v",z)
½Ð°Ñ¦Ò¡I

p212

¦^´_ 3# t8899
©Î±N1#°Ýªº¤½¦¡=SUMIF(H3:H301,"v",F3:F301)§ï³y¦¨
=SUMPRODUCT((y="v")*z)
½Ð°Ñ¦Ò¡I

ML089

¦^´_ 3# t8899

¦¡¤@ =SUMPRODUCT((H3:H301="v")*(ROW(H3:H301)>MATCH(9^9,F:F)-30),F3:F301)

¦¡¤G =SUMIF(OFFSET(H1,MATCH(9^9,E:E)-1,,-30),"v",OFFSET(F1,MATCH(9^9,E:E)-1,,-30))