Getting "0" into cell if "0" is there Options - again

[Esradekan]

Problem still exists, but thanks to Bob for his effort, so posting
again.

I have a workbook for sport, games listed on one worksheet and
results
broken down to 'Team Sheets' from the results. I am using this
formula here to break down the results onto "team sheets":


=IF(SUMPRODUCT(--(HomeTeam=$B$2),--
(AwayTeam=A5),HomeScore)<1,"",SUMPRODUCT(--(HomeTeam=$B$2),--
(AwayTeam=A5),HomeScore))


Problem is, it wont put in a score of "0" when there is one and when
I
change the <1 part of the formula to ="", it puts a '0' in every cell
until there is a positive number in the corresponding source cell.
(Did I explain that right??). Is there a remedy for the problem?


Hope someone can help.

 

[Don Guillett Excel MVP]

Problem still exists, but thanks to Bob for his effort, so posting
again.

I have a workbook for sport, games listed on one worksheet and
results
broken down to 'Team Sheets' from the results.  I am using this
formula here to break down the results onto "team sheets":

=IF(SUMPRODUCT(--(HomeTeam=$B$2),--
(AwayTeam=A5),HomeScore)<1,"",SUMPRODUCT(--(HomeTeam=$B$2),--
(AwayTeam=A5),HomeScore))

Problem is, it wont put in a score of "0" when there is one and when
I
change the <1 part of the formula to ="", it puts a '0' in every cell
until there is a positive number in the corresponding source cell.
(Did I explain that right??).  Is there a remedy for the problem?

Hope someone can help.

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Esradekan]

"If desired, send your file to dguillett  @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."- Hide quoted text -

- Show quoted text -

Hi all,

Dons solution works well, ty Don.

Basically its variations of this:

=IF(SUMPRODUCT(((HomeTeam=$B$2)*(HomeScore<>""))*((AwayTeam=
$A3)*(AwayScore<>"")))>0,SUMPRODUCT((HomeTeam=$B
$2)*(HomeScore<>"")*(AwayTeam=$A3)*HomeScore),"")

but it avoids all the zeros from appearing unnecessarily.

Again, thanks Don.

Esra