On Sat, 7 Jan 2006 20:30:15 -0600, mahou
<mahou.21ao8a_1136687701.3708@excelforum-nospam.com> wrote:

>
>When i use the MONTH(A3) Function and A3 is blank, excel takes it as a
>0! ie. MONTH(0) and returns January as the month because the date in
>serial is 00-Jan-1900. How do i get around this! Here is my Formula:
>
>I am trying to count the number of months that are used, i have done
>this with this formula below: It works perfect for all months except
>for Jan! because it think that blanks cells are a serial date 0.
>
>{=SUM(IF(MONTH(DATA!A2:A7)=1,1,0))}

=SUMPRODUCT(ISNUMBER(Data!A2:A7)*(MONTH(Data!A2:A7=1)))

Just enter as a normal formula.







>
>__________________
>| A |
>1 | Date |
>2 | 01-01-05 |
>3 | 04-04-05 |
>4 | 04-04-05 |
>5 | |
>6 | 01-01-05 |
>7 | |
>----------------------
>
>* T h e a b o v e f o r m u l a a n d d a t a r e t u r n s
>J a n C o u n t = 4 , b u t a s y o u c a n s e e t h
>e r e i s o n l y 2 J a n u a r y ! *
>: m a d : : c o n f u s e d :
>P L E A S E H E L P ! L o l
>
>I have tried to use the ISBLANK function but i could not work out how
>to incorporate it into my problem.
>
>I did Ctrl - Shift - Enter to do an array also as you can see by
>bracets..
>
>I also Attach a pic of my problem~ Hope it helps!
>
>Matt.
>
>
>+-------------------------------------------------------------------+
>|Filename: problem.JPG |
>|Download: http://www.excelforum.com/attachment.php?postid=4193 |
>+-------------------------------------------------------------------+

--ron