·j´M ¨Ã­p¼Æ °ÝÃD

mycmyc

½Ð°Ý  
a15  99101501
a16  99101502
a17  99101503
a18  99101601


¤W¦³ 3µ§991015¬Û¦P  COUNTIF(A15:A18,99101501) ­È¬°1    COUNTIF(A15:A18,991015) ÅÜ0
¦ý§Ú­n·j´M ­È¬°  991015  À³¸Ó¦³¤Tµ§   ¥Î COUNTIF(LEFT(A15,6): LEFT(A18,6),991015)  ¤£¦æ
«ç»ò°µ¤ñ¸û¦n©O
§Ú¬O·Q¥Î¤é´Á§@½s¸¹ ¤S¤£Åý¥L­«½Æ

·PÁ¤j®a«ü¾É ²×©ó§¹¦¨  
99101500        99/10/15
99101600        99/10/16
99101501        99/10/15
99101502        99/10/15
99101503        99/10/15
99101504        99/10/15
¤½¦¡ =IF(B19="","",IF(COUNTIF(A14:A18,LEFT(TEXT(B19,"ee/mm/dd"),2)&MID(TEXT(B19,"ee/mm/dd"),4,2)&MID(TEXT(B19,"ee/mm/dd"),7,2)&"*")>0,TEXT(VALUE(LEFT(TEXT(B19,"ee/mm/dd"),2)&MID(TEXT(B19,"ee/mm/dd"),4,2)&MID(TEXT(B19,"ee/mm/dd"),7,2)&"00")+COUNTIF(A14:A18,LEFT(TEXT(B19,"ee/mm/dd"),2)&MID(TEXT(B19,"ee/mm/dd"),4,2)&MID(TEXT(B19,"ee/mm/dd"),7,2)&"*"),"0"),LEFT(TEXT(B19,"ee/mm/dd"),2)&MID(TEXT(B19,"ee/mm/dd"),4,2)&MID(TEXT(B19,"ee/mm/dd"),7,2)&"00"))
¦ý COUNTIF(A14:A18,xxxxx)  ¹J¨ì°ÝÃD  §Ú¥Î COUNTIF ·j´M »Ý¦Û a14¨ì¥Ø«e¿é¤JÀxÂÃ®æ ¼g¦n  ½Æ»s ¶K¤W  A14 ·|ÅÜ°Ê  
¦³¿ìªk §â COUNTIF(A14:A18,xxxxx)   A14  ©T©w¶Ü
¤½¦¡¦nªø ¤gªk·Ò¿û  ¦³¿ìªk§â¤½¦¡ºë²¶Ü  ÁÂÁÂ

Hsieh

¦^´_ 1# mycmyc
A15:A18­Y¬°¼Æ¦r«¬ºA¸ê®Æ
=SUMPRODUCT((A15:A18>99101500)*(A15:A18<99101600))

A15:A18­Y¬°¤å¦r«¬ºA¸ê®Æ
=COUNTIF(A15:A18,"991015*")

GBKEE

¦^´_ 2# Hsieh
HsiehªO¥D: À³¸Ó¬O=SUMPRODUCT((A15:A18>=99101500)*(A15:A18<99101600))
=COUNTIF(A15:A18, ">=99101500")-COUNTIF(A15:A18,">=99101600")

Hsieh

¦^´_ 3# GBKEE


    ¨º´N¬Ý·í¤é½s¸¹±q00¶}©l©Î¬O01¶}¨Ï©l½s¸¹Åo

mycmyc

·PÁ¤G¦ì¤j¤j  §Ú¸Õ¸Õ¬Ý ÁÂÁÂ

toomellowhaw

=SUMPRODUCT(--(LEFT(A15:A18,6)="991015"))

mycmyc

¦^´_ 6# toomellowhaw

¦n©_©Ç¥Îªk ¦ý«Üºë² ¤£­­  ¼Æ¦r©Î¤å¦r ÁÂÁÂ

COUNTIF(A14:A18,xxxxx)  ¹J¨ì°ÝÃD  §Ú¥Î COUNTIF ·j´M »Ý¦Û a14¨ì¥Ø«e¿é¤JÀxÂÃ®æ ¼g¦n  ½Æ»s ¶K¤W  A14 ·|ÅÜ°Ê  
¦³¿ìªk §â COUNTIF(A14:A18,xxxxx)   A14  ©T©w¶Ü
  ½Æ»s ¶K¤W  A14 ¯à¤£Åܰʶܠ  
   
   ¤@¦æ¤@¦æ§ï  ·|²´ªá

toomellowhaw

¦^´_ 7# mycmyc

A14 ---> $A$14

mycmyc

¦^´_ 8# toomellowhaw

ÁÂÁÂ

mycmyc

§Ú¬O·Q¥Î¤é´Á§@½s¸¹ ¤S¤£Åý¥L­«½Æ
¦³§ó²³æ§@ªk¶Ü