D
Daniel
I would like to create a formula that counts and sums cell ranges based upon
multiple criteria, including a date. The date criteria seems to be the
problem. I have tried various iterations of sumif, countif, sumproduct but
can't get it correct. It usually displays #VALUE! or a completely whacked
out number.
I have named ranges for each column. Note, on the actual spreadhseet the
columns are not adjoining. Also, the dates are not in any order.
a b c d
1 Name Score Date Rating
2 Andy 1.00 09/30/2004
3 Betty 0.37 09/......
4 Carol 0.40 ....
5 David 0.84 ....
6 Andy 0.72 09/25/2004
7 Fred 0.24 ....
8 Andy 0.92 08/12/2004
The forumla would be placed in the rating column. For each row the formula
would identify all of the instances where the name in that row is found in
the
range "NAME" and where the dates in the range "DATE" are prior to the date
in that row. Where these two things are true, it will take an average of the
scores (or sum them and divide them by the count of them).
In short, find a person's average score preceding the present entry. In the
case of row 2, the Rating would be 0.82.
A million thanks.
multiple criteria, including a date. The date criteria seems to be the
problem. I have tried various iterations of sumif, countif, sumproduct but
can't get it correct. It usually displays #VALUE! or a completely whacked
out number.
I have named ranges for each column. Note, on the actual spreadhseet the
columns are not adjoining. Also, the dates are not in any order.
a b c d
1 Name Score Date Rating
2 Andy 1.00 09/30/2004
3 Betty 0.37 09/......
4 Carol 0.40 ....
5 David 0.84 ....
6 Andy 0.72 09/25/2004
7 Fred 0.24 ....
8 Andy 0.92 08/12/2004
The forumla would be placed in the rating column. For each row the formula
would identify all of the instances where the name in that row is found in
the
range "NAME" and where the dates in the range "DATE" are prior to the date
in that row. Where these two things are true, it will take an average of the
scores (or sum them and divide them by the count of them).
In short, find a person's average score preceding the present entry. In the
case of row 2, the Rating would be 0.82.
A million thanks.