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?
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?