Multiple conditions including date

[Guest]

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.

 

[Jason Morin]

Try this in D2, press ctrl/shift/enter, and fill down:

=AVERAGE(IF((name=A2)*(date<C2),score))

HTH
Jason
Atlanta, GA

 

[duane]

names in a3:a9, scores in b3:b9, dates in c3:c9, place this in d3:d9

=IF(SUMPRODUCT(($A$3:$A$9=A3)*($C$3:$C$9<C3))=0,0,SUMPRODUCT(($A$3:$A$9=A3)*($C$3:$C$9<C3)*($B$3:$B$9))/SUMPRODUCT(($A$3:$A$9=A3)*($C$3:$C$9<C3))

 

[duane]

Jason, I got an error for instance where there was nothing to average

 

[duane]

=IF(ISERROR(AVERAGE(IF(($A$3:$A$9=A3)*($C$3:$C$9<C3),($B$3:$B$9)))),0,AVERAGE(IF(($A$3:$A$9=A3)*($C$3:$C$9<C3),($B$3:$B$9))))

this solved it...gains control/shift/ente

 

[Guest]

That's a no go on all of the solutions. I get strange numbers, sums
including the row to which I am comparing the date, and on the last attempt I
get the #VALUE!. All of these state circular reference but I do not se where
it would be.

 

[Guest]

Scratch that, I think I got it. I'll say thanks a million again as I verify
some more. There are some truly gifted people in here and very generous with
their time. Thank you so much.

BTW, thanks to help such as yours, my spreadsheet has been reduced from the
need for approximately 450,000 cell formulae to less than 100. No
exaggeration. Incredible stuff!

 

[Guest]

Argh, works up to a point. It is adding the scores but not averaging them.

I tried this iteration but it results in #VALUE!

=IF((SUMPRODUCT((Player=C104)*(start_time<K104))=0),"n/a",
AVERAGE(SUMPRODUCT((Player=C104)*(start_time<K104),(Score))))

 

[Guest]

Ok, it looks like this works

=IF((SUMPRODUCT((Player=C108)*(start_time<K108))=0),"n/a",
SUMPRODUCT(--(Player=$C108),--(start_time<K108),(Score))/SUMPRODUCT((Player=$C108)*(start_time<K108)))

Note the use of the unary operator.

 

[Bob Phillips]

You might want to try this small adjustment

=IF((SUMPRODUCT((Player=C108)*(start_time<K108))=0,NA(),SUMPRODUCT(--(Player
=$C108),--(start_time<K108),(Score))/SUMPRODUCT((Player=$C108)*(start_time<K
108)))

 

[Aladin Akyurek]

Why don't you use the unary operator in the condition of IF too?

The following set up would work a bit faster:

Y2:

=SUMPRODUCT(--(Player=C108),--(start_time<K108))

X2:

=IF(Y2,AVERAGE(IF((Player=C108)*(start_time<K108),Score)),"n/a")

which you must confirm with control+shift+enter instead of just with enter.

X2 is the result cell.