Date Function Help

[shelfish]

I need some assistance writing a formula that will count how many
dates (in a list of dates in m/d/y format) are in each month. So...

Column A [1/1/08, 2/7/08, 10/19/08, ...]
Column B [Months, Jan, Feb, Mar, ...]
Column C [Counts, {count of dates in January}, {count of dates in
February}, ...]

Make sense?

The obvious answer would be to use another column to extract the month
values and then just perform a COUNTIF function. But I can't use an
extra column.

Any help is appricated.

Thanks,
Shelton

 

[Pete_UK]

Try this in C1:

=SUMPRODUCT(--(MONTH(A$1:A$100)=ROW(A1)),--((A$1:A$100)<>""))

and copy down. This ignores years, so dates from January 2007 and from
January 2008 will be counted in with the January totals.

Hope this helps.

Pete

 

[Ron Rosenfeld]

I need some assistance writing a formula that will count how many
dates (in a list of dates in m/d/y format) are in each month. So...

Column A [1/1/08, 2/7/08, 10/19/08, ...]
Column B [Months, Jan, Feb, Mar, ...]
Column C [Counts, {count of dates in January}, {count of dates in
February}, ...]

Make sense?

The obvious answer would be to use another column to extract the month
values and then just perform a COUNTIF function. But I can't use an
extra column.

Any help is appricated.

Thanks,
Shelton

Something like:

=SUMPRODUCT(--(MONTH(Dts)*(LEN(Dts)>0)=ROWS($1:1)))

entered in the column next to where you have Jan, and fill down to Dec, will
give you a count for each month.

--ron

 

[shelfish]

Wow! I consider myself pretty advanced but you got me.

I'm getting a #VALUE error.

I see the two arrays in the formula, but I don't have any blanks so I
should only need the first one right?

I replaced the range you had with my actual range...no luck
I replaced the range you had with the name of the
range("claim_date")...no luck
I tried it as an {array function}...no luck...and not...no luck

Here's exactly what I have now:
=SUMPRODUCT(--(MONTH(claim_date)=ROW(A1)),--((claim_date)<>""))

Question: What do the double-dashes mean?

Thanks again!

S.

 

[shelfish]

Ron,

That works well. Still, what function do the double dashes perform?

Many thanks for the help.
S.

 

[Ron Rosenfeld]

Ron,

That works well. Still, what function do the double dashes perform?

Many thanks for the help.
S.

The double dashes convert the Boolean value into a number. In Excel TRUE
converts to 1 and FALSE to zero.
--ron

 

[Pete_UK]

It worked fine for me in my tests. Are you sure you have proper dates
in column A?

Pete