Summing months from weeks

I need to find out for how many months each person in my database has been making payments to their retirement account. This would be realtively easy, except for the provision that if they contributed any amount during a month, it counts as a contribution. if they contributed for only one week (or one day) of a month, or if they contributed for thirty-one days, it counts as one month's contribution.

I can copy and paste a column containing each week in which the people have been making contributions into Excel. it might look like:

12/12/1998
12/5/1998
11/28/1998
11/21/1998
11/14/1998
11/7/1998
10/31/1998
10/24/1998

This would count as three months' contributions. Unfortunately this is the only data I have on their contributions.

Of course most of the columns are much longer (up to 500 entries long). I'd like to create a formula, or some other kind of operation, that would count the months listed.

I'd also like to know (just for my own knowledge) how I could count the number of weeks in this same problem. obviously you could subtract the top row number from the bottom row number and enter it manually but it would be nice if this could be done automatically as soon as you copy and paste the column into the worksheet.

Any suggestions about how to do this problem are appreciated.

Try (assuming dates are in A1:A10):

=SUM(IF(FREQUENCY(VALUE(MONTH(A1:A10)&YEAR(A1:A10)),VALUE(MONTH(A1:A10)&YEAR(A1:A10)))>0,1))

- Clay
Excel Help

Originally Posted by clayv

Try (assuming dates are in A1:A10):

=SUM(IF(FREQUENCY(VALUE(MONTH(A1:A10)&YEAR(A1:A10)),VALUE(MONTH(A1:A10)&YEAR(A1:A10)))>0,1))

- Clay
Excel Help

This formula works perfectly. I extended the second row value to 1000 so that I can copy and paste large columns in from Access. when I delete the columns though, the formula changes because it's confused. The first time it switched to 841 (I think because I scrolled down that low but I don't remember). I don't know if there's a way I can keep the formula the same. any ideas? anyone know why this happens?

actually it looks like the formula also doesn't work when there aren't a uniform amount of weeks listed. Sometimes the information for the members' participation is only displayed once a month, as opposed to the regular four times a month, so this could cause confusion with the formula.

I think I'm not explaining it very well but maybe someone has an idea for what I'm talking about?

On your first reply the solution would be to use a named range that adjust dynamically. For example:
Value_Range defined as =OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A),1)

And then change the formula to read:
=SUM(IF(FREQUENCY(VALUE(MONTH(Value_Range)&YEAR(Value_Range) ),VALUE(MONTH(Value_Range)&YEAR(Value_Range)))>0,1))

Now, instead of deleting the column, just clear the contents with a CNTRL-DELETE.

On your second, it does not confuse the formula. In my sample that I created I used the following date values:
11/12/05
11/13/05
12/14/05
11/11/06
4/5/06
4/8/06


And correctly answered that the member participated in 4 months (11/05, 12/05, 11/06, & 4/06).

- Clay
Excel Help