collating team/game scores

[Sheila]

OK, i have the following:

A summary of games played during the season, home and away. What I
need to do is to have them put on each 'Team Sheet' as the scores are
entered on the first sheet (Rounds). On this sheet, games are listed
as follows:

Home Team Score (A Column, A1, A2, A3 etc)
Home Team (B Column)
Away Team (D Column)
Away Score (E Column)

Sheet Team (each team has its own sheet) name is in A4 of that sheet,
opposing team is in b4 What I need to do is to get the home and away
scores for each team, against each team. I have whats under, but 2
dont work. Could anybody tell me why? Must be something logical
because it seems both formulas requiring HomeScore dont work.

All formulas entered as CSE as advised

1: Sheet Team Score (Home Game) (Doesnt work)
=SUMPRODUCT(--(HomeTeam=A4)*(--(AwayTeam=B4)*(HomeScore)))

2. Opposing Team Score (Sheet Team Home Game) (Works)
=SUMPRODUCT(--(AwayTeam=B4)*(--(HomeTeam=A4)*(AwayScore)))

3. Opposing Team Score (Opposing Team Home Game) (Works)
=SUMPRODUCT(--(HomeTeam=B4)*(--(AwayTeam=A4)*(AwayScore)))

4. Sheet Team Score (Away Game) (Doesnt Work)
=SUMPRODUCT(--(AwayTeam=A4)*(--(HomeTeam=B4)*(HomeScore)))

As I said in earlier posting, I have defined the following from Rounds
Sheet:

A2:A236 = HomeScore
B2:B236 = HomeTeam
D2236 = AwayTeam
E2:E236 = AwayScore

any ideas?? both that dont work come up with #VALUE!
and of course, I cant figure it

TIA

Sheila

 

[ryanb.]

Sheila,

Are you copying and pasting from Excel? your parens seem to be a bit off?
Try the following:

1: Sheet Team Score (Home Game) (Doesnt work)
=SUMPRODUCT(--(HomeTeam=A4)*(--(AwayTeam=B4)*(HomeScore)))
=SUMPRODUCT(--(HOMETEAM=A4),--(AWAYTEAM=B4),--(HOMESCORE))

2. Opposing Team Score (Sheet Team Home Game) (Works)
=SUMPRODUCT(--(AwayTeam=B4)*(--(HomeTeam=A4)*(AwayScore)))
=SUMPRODUCT(--(AWAYTEAM=B4),--(HOMETEAM=A4),--(AWAYSCORE))

3. Opposing Team Score (Opposing Team Home Game) (Works)
=SUMPRODUCT(--(HomeTeam=B4)*(--(AwayTeam=A4)*(AwayScore)))
=SUMPRODUCT(--(HOMETEAM=B4),--(AWAYTEAM=A4),--(AWAYSCORE))

4. Sheet Team Score (Away Game) (Doesnt Work)
=SUMPRODUCT(--(AwayTeam=A4)*(--(HomeTeam=B4)*(HomeScore)))

=SUMPRODUCT(--(AWAYTEAM-A4),--(HOMETEAM=B4),--(HOMESCORE))

I did not test this, hope it helps

 

[Sheila]

No, sorry, same result.

Thanks for your effort though.

sheila