try
=(SUMPRODUCT((I2:I95<0)*((Main!$H$2:$H$95100)*(M ain!$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

"deeds" wrote:

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%.

"bj" wrote:

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

"deeds" wrote:

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$95100)*(Main!$I$2:$I$9 5-Main!$H$2:$H$95)/(Main!$I$2:$I$95)0.5))