Have done and put that in. Thanks, never thought about INDEX/MATCH
for this purpose.

Sheila


On Tue, 17 May 2005 00:22:16 -0400, "Biff"
wrote:

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)*(A wayTeam=$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)*(A wayTeam=$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