adding =if(iserror to formula

[KC]

I've tried to add the =if(iserror to a formula, but I can't get it to work.
Can someone help me figure out what I'm doing wrong?

Original Formula:

SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333))

Formula with =if(iserror in it - tells me i'm missing a parenthesis - but I
can't figure out where

=IF(ISERROR(SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333),"",SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333))))

 

[Jacob Skaria]

Now the formula syntax is corrected;

=IF(ISERROR(SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333))),""
SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333)))

If this post helps click Yes

 

[Mike]

try this...

=IF(ISERROR(SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333)),"",SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333))))

if this doesn't work, add another ) just before the end of your if test,
before this: ,"",

 

[Jim Thomlinson]

=IF(ISERROR(SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333))),"",SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333)))

 

[Kassie]

=IF(ISERROR(SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333))),"",SUMPRODUCT(--(LOB!$B$1:$B$333=$A34),--(LOB!$D$1:$D$333=B$32),(LOB!$I$1:$I$333)))

HTH

Kassie

Replace xxx with hotmail

 

[Gord Dibben]

Select the cell(s) and run this macro to save all that typing and possible
syntax errors.

Sub ErrorTrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

You may want to consider using two cells.

Put the =sumproduct() formula in one cell (say A1) and then use:
=if(iserror(a1),"",a1)

If you don't like to see the intermediate results, you could hide the column
with the =sumproduct() formula.

Sometimes, it's better to avoid doing the same calculation twice.

(xl2007 added an =iferror() function. If you're using that version, you may
want to read about =iferror() in excel's help.)