try again, maybe 3rd time lucky,,collating 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

 

[Bob Phillips]

Sheila,

I tried it and it works fine.

Looking at the ones that don't work, it suggests that you have some bad
value in Rounds!A2:A236. Try a simple =SUM(HomeScore) somewhere, and see if
it returns #VALUE, then track it down.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Sheila]

Umm, no it doesnt, it returns the value 2376 just fine, any other
ideas, maybe?

TIA

sheila

 

[Dave Peterson]

I'd check your columns once more:

(doesn't work)
=SUMPRODUCT(--(HomeTeam=A4)*(--(AwayTeam=B4)*(HomeScore)))

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

It looks like the hometeam name is in column B and visiting in D.
but you're checking to see if hometeam=A4 and awayteam=B4.

Maybe just a typo in the post or _maybe_ a typo in the formula!

 

[Dave Peterson]

Ignore my response. I reread your message and see that you have multiple
sheets.

 

[Sheila]

thats correct, on the sheet that I am trying to put this info, I have
the home team name in A4 and the away team name in b4, its not the
same sheet as the scores are entered. its on a team sheet

thats why i NAMED the ranges, easier for formula writing of course,
dont need to list the sheet name AND the range, just the NAME

sheila

 

[Dave Peterson]

My "please ignore" message passed your response in the ether.

And you're sure that you don't have any #value! errors in any of those ranges?
(maybe a hidden row?)

 

[Dave Peterson]

And check HomeScore once more for non-numeric data.

Select HomeScore
edit|goto|special|Constants|and leave only Text selected

(do the same with with Formulas if HomeTeam has formulas)

 

[Bob Phillips]

Not really.

Do you wan t to try sending me the workbook to look at?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Sheila]

Well, do I ever feel blonde now. In the sheet where I am obtaining
the data from, i have merged fields across A1:E1 with text in them.
Of course this is in fact entered in A1 although it is centered across
C1 and this is what has caused the problem. Dave & Bob, thank you
very much, it now works. Apologies for being so stupid.

sheila