why does a sumproduct formula return a #div/0!

G

goonie

The sumproduct formula that I'm using is returning #div/0!.

Here's the formula: =SUMPRODUCT((outlook!CV2:CV1931="XNOR
OPC")*(outlook!CU2:CU1931="jpy")*outlook!CD2:CD1931)
 
G

Gary''s Student

You probably have a divide by zero error somewhere in your referenced data.
 
R

ryguy7272

You may have a zero somewhere in your array. Also, check those parentheses.
Maybe
=SUMPRODUCT((outlook!CV2:CV1931="XNOROPC")*(outlook!CU2:CU1931="jpy")*(outlook!CD2:CD1931))

or

=SUMPRODUCT(--(outlook!CV2:CV1931="XNOROPC"),--(outlook!CU2:CU1931="jpy"),--(outlook!CD2:CD1931))


HTH,
Ryan---
 
G

goonie

Thank you so much - It was driving me crazy! The formula always worked before
and now it does again!!!!
 
B

Bernie Deitrick

goonie,

Select each of the ranges (outlook!CV2:CV1931, etc), then use Edit / Go To... Special Formulas
uncheck all but "errors" and press OK. That will select your cells with errors. Or use data
filters on the ranges, and select the error values from the dropdown to show just those cells.

HTH,
Bernie
MS Excel MVP
 
Top