ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð°Ý¦p¦ó¥[Á`ªí¤¤«ü©w¤é´Á(©Î´Á¶¡)

[µo°Ý] ½Ð°Ý¦p¦ó¥[Á`ªí¤¤«ü©w¤é´Á(©Î´Á¶¡)

¦pªþªí¤¤¡A·Q­n¦bH23®Ú¾ÚF23©Òµ¹¤©¤§¦~«×¼Æ­È¥[Á`¨äHÄ椧·l¯q¤p­p¡C
§Ú±Ä¥Î¤U¦C¨ç¼Æ¤è¦¡­pºâ¤£¥X¡A¤£ª¾­þ¦ì¥ý¶i¥i¥H¤©¥H±Ð¾É«ü¥¿¤@¤U¡C
=sumif(A3:H23,">=(F24)",H3:H23)

´ú¸ÕÃD.rar (7.68 KB)

¦^´_ 1# 97forum


    =SUMPRODUCT((YEAR(A14:A23)=F24)*H14:H23)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh
ÁÂÁÂ¥ý¶iªº«ü¾É¡A¥t¥~·Q½Ð±Ð¤@­Ó°ÝÃD¡A¦pªGµ¹¤©ªº½d³ò¤¤¦³µL¼Æ­È(©ÎªÅ¥Õ)¡A´NµLªk¦¨¥ß¬O³o¼Ë¶Ü¡H
©Ò¥H¦pªG¦³·s¼Æ­È¥[¤JªÅ¥ÕÀx¦s®æ®É¥²¶·¦A¥h­×§ï¤½¦¡ªº½d³ò­È©ÎªÌ¬O¥i¥H§ï¨ä¥L¤è¦¡¡H

TOP

¦^´_ 3# 97forum
H24°}¦C¤½¦¡
=SUMPRODUCT((IF(ISNUMBER(INDIRECT("R2C1:R[-1]C1",0)),YEAR(INDIRECT("R2C1:R[-1]C1",0)),0)=F24)*INDIRECT("R2C:R[-1]C",0))
´ú¸ÕÃD.rar (8.05 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

¦¹¦¡§ï¬°

=SUMPRODUCT(--(YEAR(A14:A23)=F24),H14:H23)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# ML089

³o¼Ë¹J¨ì«D¤é´Á®É¤@¼Ë·|¥X¿ù
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

§ï¬°¤U¦¡¡A¹J¨ì«D¤é´Á´N¤£·|µo¥Í¿ù»~

=SUMPRODUCT(--(TEXT(A14:A23,"yyyy")=F24&""),H14:H23)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089


    =SUMPRODUCT((TEXT($A$2:$A23,"yyyy")=$F24&"")*($H$2:$H23))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh

ÁÙ¬Oª©¥D¼F®`¡A§ï¤F¦n´X¦¸³£§ï¿ù¡A§Æ±æ¨S»~¾É¥L¤H
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  ML089


    =SUMPRODUCT((TEXT($A$2A23,"yyyy")=$F24&"")*($H$2H23))
Hsieh µoªí©ó 2012-9-3 11:14



    ÁÂÁ±zªºÀ°¦£¡A¥i¬O¸g¹L´ú¸Õ¡A¦b¤é´ÁÄæ¦ì¤¤¦pªG¬OªÅ¥Õ(©ÎªÌ¬Onull­È)¨äµ²ªG¤´µM¬O¿ù»~ªº¡C§ÚÁÙ¦b¬ã¨s¤F¸Ñ±z©Ò´£¨Ñªº¨ç¼Æµ²ºc¡CµL½×¦p¦óÁÙ¬OÁÂÁÂ¥ý¶iªº±Ð¾É¡C

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD