When using MONTH function on Blank Cell!! Returns Month=Jan!

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))}

__________________
| A |
1 | Date |
2 | 01-01-05 |
3 | 04-04-05 |
4 | 04-04-05 |
5 | |
6 | 01-01-05 |
7 | |
----------------------

The above formula and data returns Jan Count = 4, but as you can see there is only 2 January!

PLEASE HELP! Lol

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.

Hi!

Try this (normally entered, not an array):

=SUMPRODUCT(--(ISNUMBER(DATA!A2:A7)),--(MONTH(DATA!A2:A7)=1))

Biff

"mahou" <[email protected]> wrote in
message news:[email protected]...
>
> 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))}
>
> __________________
> | 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 |
> +-------------------------------------------------------------------+
>
> --
> mahou
> ------------------------------------------------------------------------
> mahou's Profile:
> http://www.excelforum.com/member.php...o&userid=30236
> View this thread: http://www.excelforum.com/showthread...hreadid=499106
>

On Sat, 7 Jan 2006 20:30:15 -0600, mahou
<[email protected]> 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

Typo Alert:

My response should read:

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




On Sat, 7 Jan 2006 20:30:15 -0600, mahou
<[email protected]> 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))}
>
>__________________
>| 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

Hi Biff you are a fricken Legend! Ron you could be too but i used Biff's Because it was first and it worked perfect thanks heaps!!

MAtt.

=SUM(IF(AND(A2:A7)<>"",MONTH(A2:A7)=1),1,0)

This works for me. I don't get the curly brackets in Excel 2002,
though.

=SUM(IF(AND(Data!A2:A7)<>"",MONTH(Data!A2:A7)=1),1,0)

This works for me.