Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
How can I use a cell reference in Sumproduct array formula? | Excel Discussion (Misc queries) | |||
division formula | New Users to Excel | |||
Shorten sumproduct formula | Excel Discussion (Misc queries) | |||
#DIV/0! ...how to suppress when using this formula.... | Excel Discussion (Misc queries) |