Multiple conditions including date

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.
 
J

Jason Morin

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

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

HTH
Jason
Atlanta, GA
 
D

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))
 
D

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
 
D

Daniel

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.
 
D

Daniel

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!
 
D

Daniel

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))))
 
D

Daniel

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.
 
B

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)))
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top