Need help with a date calculation

[jr100]

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!

[Ron Coderre]

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)*(YEAR(A1:A10)=2006))


Does that help?

Regards,
Ron

[Peo Sjoblom]

=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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>