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

[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)

 

[Max]

One possibility: the range: outlook!CD2:CD1931 contains #DIV/0!
Check & clean it up, and it'll work fine
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Gary''s Student]

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

 

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

 

[goonie]

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

 

[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

 

[Max]

Welcome, glad to hear ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik