How to ignore #DIV/0! in Sumproduct formula

[Guest]

Below is my formula...there are #DIV/0! in the data set...(I can't remove).
I want to exclude them in the formula. Basically...how to I exclude the
#DIV/0! in the following formula? Thanks in advance...

=(SUMPRODUCT((Main!$H$2:$H$95>100)*(Main!$I$2:$I$95-Main!$H$2:$H$95)/(Main!$I$2:$I$95)>0.5))

 

[Guest]

try adding not(iserror(Range)) to your sumproduct formula

I have not been able to figure out what you are trying to calculate with the

0.5 in the sumproduct equation

the divide by zero error may come when the I column is less than 0.5

 

[Guest]

Thanks....I tried the not(iserror(Range)...but all it does is count the
entire range...without the criteria. the formula is trying to count the
occurances of when column H is greater than 100 and when the result of the
I-H/I is > than 50%.

 

[Peo Sjoblom]

Why can't you fix the formulas that return the error?

 

[Guest]

try
=(SUMPRODUCT((I2:I95<>0)*((Main!$H$2:$H$95>100)*(Main!$I$2:$I$95-Main!$H$2:$H$95)/(Main!$I$2:$I$95)>0.5))
unless there is another spot in the various ranges with /0 error
which is what I thought you had

 

[Peo Sjoblom]

If we assume that the div errors are caused by the second part of your
formula
this might work

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5),0,(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5)*(Main!H2:H95>100))


entered with ctrl + shift & enter

this assume that there are no errors in H2:H95

if there can be div errors there you could try

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5),0,(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5)*((IF(ISERROR(Main!H2:H95>100),0,Main!H2:H95>100))))

also array entered


--
Regards,

Peo Sjoblom

 

[Guest]

Thanks!...Worked great.

If we assume that the div errors are caused by the second part of your
formula
this might work

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5),0,(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5)*(Main!H2:H95>100))


entered with ctrl + shift & enter

this assume that there are no errors in H2:H95

if there can be div errors there you could try

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5),0,(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5)*((IF(ISERROR(Main!H2:H95>100),0,Main!H2:H95>100))))

also array entered

 

[Harlan Grove]

If we assume that the div errors are caused by the second part of
your formula this might work

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5),0,
(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5)*(Main!H2:H95>100))

....

OP said there were errors in the data set. You don't need the >0.5
check in the error check.

this assume that there are no errors in H2:H95

How so? Errors in H2:H95 will propagate to the ISERROR call here.

if there can be div errors there you could try

=SUM(IF(ISERROR((Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5),0,
(Main!I2:I95-Main!H2:H95)/(Main!I2:I95)>0.5)
*((IF(ISERROR(Main!H2:H95>100),0,Main!H2:H95>100))))

....

And both will capture any other errors in the data set. If the data
set contained any #REF!, #NULL! or #NAME? errors, there's seldom any
good reason to ignore them. That is, these 3 errors usually indicate
something seriously wrong, so generally best to see them rather than
mask them.

So another alternative,

=SUM(IF(ISNUMBER(1/(ERROR.TYPE(Main!H2:H95/Main!I2:I95)=2)),0,
(Main!H2:H95/Main!I2:I95<0.5)*(Main!H2:H95>100)))

which only traps #DIV/0! errors and replaces (I-H)/I>0.5 with the
algebraically identical but more efficient H/I<0.5. Of course, if the
#DIV/0! errors were due to zeros in Main!I2:I95, the obvious
alternative would be

=SUMPRODUCT(--(2*Main!H2:H95<Main!I2:I95),--(Main!H2:H95>100))