If AND for large range of data
 

I am trying to create a standings sheet based on scores of games. I have a
new worksheet and I am pulling the scores from another worksheet. What I am
trying to do is for a specific team is to find them in a list of games and
then look at the score and determine if they won or lost. If they one then I
would add one to the wins column. However, I cannot seem to figue it out
without some insanely large formula(which excel tells me is too big). Thanks
for any help.

Here is the code I was using =If(AND('sheet1'!A1='sheet2'!A1,
SUM('sheet1'!B1-'sheet1'!D1)1),1,0,+If(AND('sheet1'!A2='sheet2'!A 1,
SUM('sheet1'!B2-'sheet1'!D2)1),1,0,+ etc...

Worksheet 1 (Scores)
A B C D E
H team goals blank goals A Team
1 My team 5 3 Your Team
2 His Team 3 1 Her Team
3 Our Team 1 0 My Team

30 rows

Worksheet 2 (results)
A B C D E
F G
Team Wins Losses Ties Goals For Goals
Against Diff.
1 My Team
2 Your Team
3 His Team
4 Her Team
5 Our Team

If AND for large range of data
 

Try using a SUMPRODUCT formula to check all the rows at once, i.e.

=SUMPRODUCT(('Sheet1'!A$1:A$30=A1)*('Sheet1'!B$1:B $30'Sheet1'!D$1:D$30))

That would count the home wins

If you need to count both home and away then add another SUMPRODUCT,
i.e.

=SUMPRODUCT(('Sheet1'!A$1:A$30=A1)*('Sheet1'!B$1:B $30'Sheet1'!D$1:D$30))+SUMPRODUCT(('Sheet1'!E$1:E $30=A1)*('Sheet1'!B$1:B$30<'Sheet1'!D$1:D$30))

regards, barry


--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145332