Need help with a date calculation
 

Hi,

HELP!

Any help with this issue would be great. I'm trying to count the
number of days for a given month in a range of cells that contain
multiple months.

Example:

1/1/6
3/1/6
1/2/6
1/5/6
2/2/6
2/7/6

Answer: the number of days in January is 3, the # of days in March is
1, the number of days in February is 2.

I've tried a variety of formulas, from counti, datedif, sumif,
sumproduct, etc. and can't seem to get them to work.

Any Ideas or help would be greatly appreciated.

Thank you!


--
jr100
------------------------------------------------------------------------
jr100's Profile: http://www.excelforum.com/member.php...o&userid=31335
View this thread: http://www.excelforum.com/showthread...hreadid=510159

Need help with a date calculation
 

Try something like this:

If only the month is significant:
=SUMPRODUCT(ISNUMBER(A1:A10)*(MONTH(A1:A10)=1))

If the month and year are significant:
=SUMPRODUCT(ISNUMBER(A1:A10)*(MONTH(A1:A10)=1)*(YE AR(A1:A10)=2006))


Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=510159

Need help with a date calculation
 

=SUMPRODUCT(--(ISNUMBER(A2:A50)),--(MONTH(A2:A50)=1))

for January, for the rest of the months you only need

=SUMPRODUCT(--(MONTH(A2:A50)=2))

(for February)

the reason is that a blank cell will be interpreted as month number 1 thus
the extra condition for Jan

--
Regards,

Peo Sjoblom

Portland, Oregon




"jr100" wrote in
message ...

Hi,

HELP!

Any help with this issue would be great. I'm trying to count the
number of days for a given month in a range of cells that contain
multiple months.

Example:

1/1/6
3/1/6
1/2/6
1/5/6
2/2/6
2/7/6

Answer: the number of days in January is 3, the # of days in March is
1, the number of days in February is 2.

I've tried a variety of formulas, from counti, datedif, sumif,
sumproduct, etc. and can't seem to get them to work.

Any Ideas or help would be greatly appreciated.

Thank you!


--
jr100
------------------------------------------------------------------------
jr100's Profile:
http://www.excelforum.com/member.php...o&userid=31335
View this thread: http://www.excelforum.com/showthread...hreadid=510159