Finding ZERO

  • Thread starter Thread starter \(M\)e\(Ag\)ain
  • Start date Start date
M

\(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)
 
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.
 
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.
 
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).
 
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.
 
Back
Top