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

Hi!

Try this (normally entered, not an array):

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

Biff

"mahou" wrote in
message ...

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
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
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.


--
mahou
------------------------------------------------------------------------
mahou's Profile: http://www.excelforum.com/member.php...o&userid=30236
View this thread: http://www.excelforum.com/showthread...hreadid=499106

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

This works for me.

=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.