# Sumproduct using 1/ and DIV#0

P

#### Paul C

Using Excel 2007

I have an existing sumproduct formula I am using

=IF(\$E22=0,0,SUMPRODUCT(--('Part Data Entry'!\$A\$15:\$A\$500=\$E22),'Part Data
Entry'!\$J\$15:\$J\$500,'Part Data Entry'!\$U\$15:\$U\$500,'Part Data
Entry'!\$X\$15:\$X\$500))

I want to add the condition 1/'Part Data Entry'!\$T\$15:\$T500, but this range
by design has zero values in it.

I don't want to use a array function, since many different people use this
sheet and some have a tendency to break things like this. I also don't want
to add any more columns to the souce data sheet or the sheet were I do the
calculations since they already use have more columns than I would like.

I tried some 1/IFERROR(...) and 1/(IF(..) but could not get these to work as
a standard (non-array function).

I am lucky enough that 'Part Data Entry'!\$X\$15:\$X\$500 is only used for this
calculation and if I have to I can to the dividing by Col T here and be done
with it.
This makes Col X seem out of line with the rest of the data, but this is not
the end of the world.

I was just curious if there was a method for using 1/X in a sumproduct with
the occasional X=0 in the array?