Finding ZERO

[\(M\)e\(Ag\)ain]

Hi all
I am using the formula below works fine for me. the problem is when there is
no value to return it show 0.
How can I get rid of 0 without using conditional formating????
thanks

=SUMPRODUCT(--(BankAccount!$A$1:$A$1998=S315),--(BankAccount!$D$1:$D$1998=St
reamLine),BankAccount!$E$1:$E$1998)

 

[Max]

Try Tool > Options > View tab
Uncheck 'Zero values' > OK

hth
Max

 

[\(M\)e\(Ag\)ain]

thanks MAX
but I meant to add something more in the formula.

 

[ryanb.]

If you want the cell to return blank, this may help:

=IF(ISNA(MATCH(S315,BankAccount!$A$1:$A$1998,FALSE)),"",IF(ISNA(MATCH(Stream
Line,BankAccount!$D$1:$D$1998,FALSE)),"",paste your sumproduct formula here)

ryanb.

 

[\(M\)e\(Ag\)ain]

thanks ryanb but then I tired the simple IF statement and it worked.

=IF(SUMPRODUCT(--(BankAccount!$A$1:$A$1998=S321),--(BankAccount!$D$1:$D$1998
=StreamLine),BankAccount!$E$1:$E$1998)=0,"",SUMPRODUCT(--(BankAccount!$A$1:$
A$1998=S321),--(BankAccount!$D$1:$D$1998=StreamLine),BankAccount!$E$1:$E$199
8))

I think I will stick to it.
thanks for your tip anyway.

 

[Tom Ogilvy]

That certainly works, but does the sumproduct twice. Since sumproduct is
pretty slow, you should begin to see a slowdown in recalculation using this
approach (unless you only have one or two of these formulas).

 

[\(M\)e\(Ag\)ain]

Yeah Tom it did slow down the Bank WorkSheet but its worth it.
thanks.

 

[Tom Ogilvy]

If RyanB gave you a faster but equivalent solution?

I guess I am missing the objective. Or maybe Ryan's solution didn't work -
but his would return a zero if there was an entry with zero.