"Peo Sjoblom" wrote...
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:H95100))
....

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:H95100),0,Main!H2:H95100) )))
....

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:H95100)))

which only traps #DIV/0! errors and replaces (I-H)/I0.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:H95100))