Problem with a Sumproduct formula

[Michelle]

Hi, I'm having a problem with a sumproduct formula thats not returning data
consistently.

I have a datasheet that has all the formulas in that is working fine. I then
have a summary sheet for the managers which allows them to choose what they
want to look at and the sheet populates using sumproduct formulas that
reference off the datasheet. The summary sheet looks like this:

Product (which is a drop down)

Region Measure Q1 Q2 Q3 Q4 etc
WCE Conversion rate 0% 0% 0% 0%
WCE Indirect 37% 40% 39% 15%

etc
The sumproduct formula is:
=(SUMPRODUCT((DSReg=$A5)*(DSProd=$B$2)*(DSKPI=$B5),DSQ109Amt))
DSREG is the region, DSProd is the product in the drop down, DSKPI is the
Measure eh Conversion rate.
I've double checked all the range's agree and the names are right, and the
reference cells tie up. Has anyone any idea why the formula is working
correctly for Indirect but not for Conversion rate?

I've done some experimenting and I'm fairly sure that its the range DSKPI
thats causing the problem- Ive tried deleting it and redoing the range but
that didn't fix it.

 

[Kassie]

Hi Michelle

Zero multiplied by anything, and anything multiplied by zero, will always be
zero!
In your sample, all your conversion rates are zero. You will have to cater
for situations where you work with a zero conversion rate, and when it is
zero, leave that part out of the formula. You can do this with an IF
statement.

=IF($B5=0,(SUMPRODUCT((DSReg=$A5)*(DSProd=$B$2)*),DSQ109Amt)),((SUMPRODUCT((DSReg=$A5)*(DSProd=$B$2)*(DSKPI=$B5),DSQ109Amt))).
Obvioulsy not tested, but something along those lines

--
HTH

Kassie

Replace xxx with hotmail

 

[Michelle]

Hi Kassie,

Sorry I should have made it clearer.... In the datasheet the conversion
rates are not zero they are numbers such as 40% 25% etc. But the summary
sheet which is referencing the datasheet isn't seeing them, its populating
with zero instead.

 

[Michelle]

Bump....