DIV/0! error in SumProduct formula with no division
 

Hi

I'm building a recruitment register and I've come up with a DIV/O! error in a sumproduct formula where there is no division in the formula - I have several sumproduct formulas and named ranges (all named ranges are the same length) that work, except for this particular formula.

Basically the formula is (where Position etc are named ranges):
=sumproduct(((Position=a1)*(Evaluation_Grade=a2)*( Source_Ref="Not on List")))

(A1 is text, A2 is a numeric value)
All my other sumproduct formulas are working that use the Position and Evaluation_Grade Named Range.

The Source_Ref range was previously working on an older version. As mentioned all Named ranges are the same length and the text in the formula has been copied from the data sample to ensure accuracy.

I've tried auditing and evaluating the error and nothing explains the DIV/0! error.

Any solutions would be mucly appreciated.

M.

Error values propagate through formulas. Likely there is a #DIV/0!
error in one of the named ranges. If appropriate, you could wrap the
ranges in an IF to strip out errors as in
IF(ISERROR(range),"",range)

Jerry

Irrational wrote:

Hi

I'm building a recruitment register and I've come up with a DIV/O!
error in a sumproduct formula where there is no division in the formula
- I have several sumproduct formulas and named ranges (all named ranges
are the same length) that work, except for this particular formula.

Basically the formula is (where Position etc are named ranges):
=sumproduct(((Position=a1)*(Evaluation_Grade=a2)*( Source_Ref="Not on
List")))

(A1 is text, A2 is a numeric value)
All my other sumproduct formulas are working that use the Position and
Evaluation_Grade Named Range.

The Source_Ref range was previously working on an older version. As
mentioned all Named ranges are the same length and the text in the
formula has been copied from the data sample to ensure accuracy.

I've tried auditing and evaluating the error and nothing explains the
DIV/0! error.

Any solutions would be mucly appreciated.

M.