kimbal

¦^´_ 1# amu1129

sumproduct+subtotal
F11¤½¦¡: °²³]±ø¥ó¦b F8©MF9
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(C2:C6,ROW(C2:C6)-ROW(C2),0,1)),(D2:D6))

¤U¸ü (82.34 KB)

2010-8-14 21:59

t20100814sumproductsubtotal.zip (4.71 KB)

¤U¸ü¦¸¼Æ: 15

2010-8-14 22:00

¦ý¬O...ÁÙ¬O«Øij¦h¥Î¼Ï¯Ãªí

kimbal

¦^´_ 3# amu1129

·Q¤À§OÄæ¦ìªº¸Ü,³o¼Ë´N¥i¥H¤F
c:
    =SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(C2:C6,ROW(C2:C6)-ROW(C2),0,1)))
d:
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(D2:D6,ROW(D2:D6)-ROW(D2),0,1)))

³o­Ó­n¥Î¤WOFFSET¤F, ª½±µ¥ÎSUBTOTAL(109,D2:D6) ·|Åܦ¨¹ê¼Æ,¤£¬O°}¦C

kimbal

¦^´_ 5# amu1129

¤½¦¡¥i¥H¼g¦¨³o­Ó¼Ë¤l:
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(C2,ROW(C2:C6)-2,0)))

ÁÙ­ì°ò¥»¨B, «e¥b¨Bªº²z¸Ñ¶Ü?
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9))

³o¥y¥i¥H¸ÑÄÀ¬°
   --(B2=F8) * --(A2=F9)
+ --(B3=F8) * --(A3=F9)
+ ....
+ --(B6=F8) * --(A6=F9)

-- ¥Î¨Ó§â§PÂ_±ø¥ó´«¦¨1 / 0, 1´N¬OB2=F8 ªº·N«ä


¥[¤F " ,SUBTOTAL(109,OFFSET(C2,ROW(C2:C6)-2,0)) " «á

   --(B2=F8) * --(A2=F9) * SUBTOTAL(109,C2)
+--(B3=F8) * --(A3=F9) * SUBTOTAL(109,C3)
...
+--(B6=F8) * --(A6=F9) * SUBTOTAL(109,C6)

¦^¤W¤@¨B
   --(B2=F8) * --(A2=F9) * SUBTOTAL(109,OFFSET(C2,0,0))
+--(B3=F8) * --(A3=F9) * SUBTOTAL(109,OFFSET(C2,1,0))
...
+--(B6=F8) * --(A6=F9) * SUBTOTAL(109,OFFSET(C2,4,0))

¦A¦^¤W¤@¨B
   --(B2=F8) * --(A2=F9) * SUBTOTAL(109,OFFSET(C2,ROW(C2)-2,0))
+--(B3=F8) * --(A3=F9) * SUBTOTAL(109,OFFSET(C2,ROW(C3)-2,0))
...
+--(B6=F8) * --(A6=F9) * SUBTOTAL(109,OFFSET(C2,ROW(C6)-2,0))

kimbal

¦^´_ 7# amu1129

ROW(C2:C6)-2
¦^¶Ç¥X¨Óªº¬O 2-2,3-2,4-2,5-2,6-2 §Y 0,1,2,3,4
°t¦Xoffset¨Ó¥Î,
OFFSET(C2,0,0) ¦^¶Ç C2 ¦Û¤v
OFFSET(C2,1,0) ¦^¶Ç C3
OFFSET(C2,2,0) ¦^¶Ç C4
OFFSET(C2,3,0) ¦^¶Ç C5
OFFSET(C2,4,0) ¦^¶Ç C6