I have a workbook that shows scores from games during the year and a
sheet for each team that shows their respective home and away scores,
taken from the main list of games played.

the formula i am using to put these teams game scores on their
respective sheet is as follows:

{=(SUMPRODUCT(--(HomeTeam=$C$2)*(--(AwayTeam=B5)*(HomeScore)))}

&

{=(SUMPRODUCT(--(HomeTeam=$C$2)*(--(AwayTeam=B5)*(AwayScore)))}

This shows the home team and the away teams score and it is adapted
for the return game.

This is an array formula and it uses 4 specified arrays,HomeTeam,
AwayTeam and AwayScore.

My problem is this. Without changing the summary sheet to NOT show
zero values, EVERY game until played shows as 0 - 0. Doesnt allow for
true records until seasons end. Now, is there an adaption of this
array formula to show NOTHING in the cells until the game is played?

TIA

Sheila.

First, while it's an array formula, it shouldn't be entered with
CTRL-SHIFT-ENTER.

Second, if you're using *, which means you're multiplying the arrays
before they're passed to SUMPRODUCT, you don't need the --. OTOH, the --
syntax is slightly more efficient.

Try:

=SUMPRODUCT(--(HomeTeam=$C$2),--(AwayTeam=B5), HomeScore)

I'm not sure I follow how you're determining that the game hasn't been
played yet.

However, you may want to define HomeTeam, AwayTeam and HomeScore as
dynamic ranges, so they only contain the played games. See

http://cpearson.com/excel/named.htm#Dynamic




In article ,
Sheila wrote:

I have a workbook that shows scores from games during the year and a
sheet for each team that shows their respective home and away scores,
taken from the main list of games played.

the formula i am using to put these teams game scores on their
respective sheet is as follows:

{=(SUMPRODUCT(--(HomeTeam=$C$2)*(--(AwayTeam=B5)*(HomeScore)))}

&

{=(SUMPRODUCT(--(HomeTeam=$C$2)*(--(AwayTeam=B5)*(AwayScore)))}

This shows the home team and the away teams score and it is adapted
for the return game.

This is an array formula and it uses 4 specified arrays,HomeTeam,
AwayTeam and AwayScore.

My problem is this. Without changing the summary sheet to NOT show
zero values, EVERY game until played shows as 0 - 0. Doesnt allow for
true records until seasons end. Now, is there an adaption of this
array formula to show NOTHING in the cells until the game is played?

1. there is no need to array enter sumproduct formulas
2. You should use either unary minuses or multiply the arrays, not both, so
either


=SUMPRODUCT(--(HomeTeam=$C$2),--(AwayTeam=B5),HomeScore)

or


=SUMPRODUCT((HomeTeam=$C$2)*(AwayTeam=B5)*(HomeSco re))


what is the result you desire and what is the result you get, or rather hoe
do you determine a game has been played?

--
Regards,

Peo Sjoblom


"Sheila" wrote in message
...
I have a workbook that shows scores from games during the year and a
sheet for each team that shows their respective home and away scores,
taken from the main list of games played.

the formula i am using to put these teams game scores on their
respective sheet is as follows:

{=(SUMPRODUCT(--(HomeTeam=$C$2)*(--(AwayTeam=B5)*(HomeScore)))}

&

{=(SUMPRODUCT(--(HomeTeam=$C$2)*(--(AwayTeam=B5)*(AwayScore)))}

This shows the home team and the away teams score and it is adapted
for the return game.

This is an array formula and it uses 4 specified arrays,HomeTeam,
AwayTeam and AwayScore.

My problem is this. Without changing the summary sheet to NOT show
zero values, EVERY game until played shows as 0 - 0. Doesnt allow for
true records until seasons end. Now, is there an adaption of this
array formula to show NOTHING in the cells until the game is played?

TIA

Sheila.

Thanks for this, this simplifys the formula somewhat, but still it
shows 0 - game score if the game hasnt been played yet, unless i
uncheck to display zero values, which isnt good because then it doesnt
show anything for games that have been played and a team scored zero.
this is what i am trying to get around.

any help?

sheila
TIA




On Wed, 11 May 2005 20:44:49 -0600, JE McGimpsey
wrote:

First, while it's an array formula, it shouldn't be entered with
CTRL-SHIFT-ENTER.

Second, if you're using *, which means you're multiplying the arrays
before they're passed to SUMPRODUCT, you don't need the --. OTOH, the --
syntax is slightly more efficient.

Try:

=SUMPRODUCT(--(HomeTeam=$C$2),--(AwayTeam=B5), HomeScore)

I'm not sure I follow how you're determining that the game hasn't been
played yet.

However, you may want to define HomeTeam, AwayTeam and HomeScore as
dynamic ranges, so they only contain the played games. See

http://cpearson.com/excel/named.htm#Dynamic




In article ,
Sheila wrote:

I have a workbook that shows scores from games during the year and a
sheet for each team that shows their respective home and away scores,
taken from the main list of games played.

the formula i am using to put these teams game scores on their
respective sheet is as follows:

{=(SUMPRODUCT(--(HomeTeam=$C$2)*(--(AwayTeam=B5)*(HomeScore)))}

&

{=(SUMPRODUCT(--(HomeTeam=$C$2)*(--(AwayTeam=B5)*(AwayScore)))}

This shows the home team and the away teams score and it is adapted
for the return game.

This is an array formula and it uses 4 specified arrays,HomeTeam,
AwayTeam and AwayScore.

My problem is this. Without changing the summary sheet to NOT show
zero values, EVERY game until played shows as 0 - 0. Doesnt allow for
true records until seasons end. Now, is there an adaption of this
array formula to show NOTHING in the cells until the game is played?