Oh hey, I came up with something slightly more efficient. I used an INDEX
function in place of OFFSET.

Check your inbox!

Biff

"Sheila" (remove underscores) wrote in message
...
and it works just fine Biff, thanks very much

sheila


On Mon, 16 May 2005 14:47:57 -0400, "Biff"
wrote:

If anybody is still following this thread....

Sheila sent me her file.

The problem involved having to test specific cells within two different
named ranges for "". Zero is a possible score so testing for for a sum of
zero wouldn't work.

Here's what I came up with:

Array entered:

For the Home Team sco

=IF(OFFSET(Rounds!$A$1,MATCH(1,(HomeTeam=B6)*(Aw ayTeam=$C$2),0),0)="","",SUMPRODUCT(--(HomeTeam=B6),--(AwayTeam=$C$2),AwayScore))

For the Away Team sco

=IF(OFFSET(Rounds!$A$1,MATCH(1,(HomeTeam=B6)*(Aw ayTeam=$C$2),0),4)="","",SUMPRODUCT(--(HomeTeam=B6),--(AwayTeam=$C$2),HomeScore))

The file would require a lot of these formulas and calc time will be
slower.

Biff

"Sheila" wrote in message
. ..
Nope, sorry Dave, that doesnt work either.

Never mind, I will put up with it the way it is, thanks for trying.

Sheila




On Sat, 14 May 2005 07:23:33 -0500, Dave Peterson
wrote:

Ah, you only want to check if that team in $c$2 has been used:

=IF(SUMPRODUCT(--(hometeam=$C$2),--(ISNUMBER(homescore)))=0,"",
SUMPRODUCT(--(hometeam=$C$2),--(awayteam=B5),homescore))

(all one cell)

Sheila wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulaher e)


Sheila wrote:

I am using Sumproduct

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

to put team scores on a team sheet, but still get a score of 0 - 0
when the game hasnt been played and no score is entered onto the
'schedule' sheet. This is not ideal. How can i have NOTHING go
into
the cells when the game has not been played. These scores come
from
a
'schedule' sheet with all games for the year listed.

any help?

sheila
TIA