collating game scores

[Sheila]

I have 4 columns on a sheet called "Rounds" for keeping track of
scores in games involving a tournament of 15 teams. Column A
(a2:A236) is defined as HomeScore, B (B2:B236) =HomeTeam, C (C2:C236)
=AwayTeam and D (D2236) =AwayScore.

I have a sheet for each team as well and need to show each result
against each of the other teams, both home and away. These sheets
show in cell B1 the team and columns as follows:

Column A = Opposing Team
B = Sheet Team Home game score
C = Opposing Team Away Score
D = Sheet Team Away Score and
E = Opposing Team Home Score.

How would I do this?

Any help appreciated
TIA
Sheila

 

[Harry Bo]

Sheila,

As an example here is my rounds sheet:
sheet = Rounds
Score H Home team Away Team Score A
2 ABC DEF 0
2 ABC GHI 1
1 ABC JKL 3
3 DEF ABC 5
5 GHI ABC 4
6 JKL ABC 2

In Say team "ABC" :
A B C D E
ABC HOME AWAY
ABC OPP ABC OPP
DEF 2 0 5 3
GHI 2 1 4 5
JKL 1 3 2 6

A1 = ABC (The team this sheet refers to)

To get ABC Home scores =SUMPRODUCT(--(Rounds!$B$2:$B$7=$A$1)*(Rounds!$C$2:$C$7=A3)*(Rounds!$A$2:$A$7)) ( Array entered Control Shift Enter CSE)

ABC Home opponents score =SUMPRODUCT(--(Rounds!$C$2:$C$7=A3)*(Rounds!$D$2:$D$7)) again CSE

To get ABC Away Score =SUMPRODUCT(--(Rounds!$B$2:$B$7=A3)*(--(Rounds!$C$2:$C$7=$A$1)*(Rounds!$D$2:$D$7))) (CSE)

To get ABC opponent score =SUMPRODUCT(--(Rounds!$C$2:$C$7=$A$1)*(--(Rounds!$B$2:$B$7=A3)*(Rounds!$A$2:$A$7))) (CSE)


You'll have to change the C7 to C236.
hope that helps

Harry

 

[Sheila]

Hary,

thanks for the reply firstly.

I HAVE GOT formulas 2 & 3 to work fine but not 1 & 4. I made a small
adjustment to formula 2 (Home Team left out) but for the life of me I
cant get 1 & 4 to work

Any suggestions?
TIA
Sheila

 

[Harry Bo]

Sheila,

Could you post your new formulas and the table please?

Harry

 

[Sheila]

OK, i have the following:

Sheet Team name is in A4, opposing team is in b4 (I can adapt other
formulas to get other game scores)

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