Need help with a date calculation

J

jr100

Hi,

HELP!

Any help with this issue would be great. I'm trying to count th
number of days for a given month in a range of cells that contai
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 i
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
 
R

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
 
P

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
 
Top