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

  • Thread starter Thread starter goonie
  • Start date Start date
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)
 
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---
 
Thank you so much - It was driving me crazy! The formula always worked before
and now it does again!!!!
 
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
 
Back
Top