Summing months from weeks

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