Sumproduct using 1/ and DIV#0


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

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?


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct DIV/0 Error 4
#DIV/0! 2
#DIV/0! 3
#DIV/0! 1
#DIV/0 3
IF with #DIV/0! 1
DIV/0! 2
IF with #DIV/0! 2