Formula returns #VALUE! for some cells

[Neales]

Well this is going to be hard to explain without showing the spreadsheet.

Basically I have a formula

=IF(O23="","",IF(SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959),SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959)*R23,SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$L$2:$L$19959))/SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$J$2:$J$19959*4.33))))

For some reason it is returning #VALUE! in some cells. I think its something to do with the last part of the formula where it is bold.

The formula as you can see looks at data and matches relevant data to return specific depending on what has been matched. Now at the end it is matching the correct data and return that value however i want to divide the value by another (another value*4.33).

Can anyone see any problem with that formula?


<!-- / message -->

 

[Neales]

I can't see anything in that column that isn't numeric.

I tried selecting the actual cell that the last part of the formula (Container Price List'!$J$2:$J$19959*4.33) should find and that returns the correct value so it does seem as if its something to do with the J part like you say.

 

[DonkeyOte]

so this DEFINITELY doesn't work ?

IF(O23="","",IF(SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959),SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23),'Container Price List'!$F$2:$F$19959)*R23,SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$L$2:$L$19959))/SUMPRODUCT(('Container Price List'!$A$2:$A$19959=O23)*('Container Price List'!$C$2:$C$19959=S23)*('Container Price List'!$D$2:$D$19959=P23)*('Container Price List'!$B$2:$B$19959=T23)*('Container Price List'!$J$2:$J$19959)*4.33)))

 

[Neales]

No it just returns the same as before #VALUE!

 

[DonkeyOte]

does

=COUNT('Container Price List'!$J$2:$J$19959)

= 19958 ?

 

[Neales]

No it equals 659.

 

[DonkeyOte]

OK well that implies you have over 13000 non-numerics in your J range which will cause the failure -- the data in J must be numeric.

 

[Stormseed]

Neales,

SUMPRODUCT() only works with numeric values. You should convert all the text values to numeric for the SUMPRODUCT() to work successfully. You can do this by the double unary operator(--). Click here for detailed help on using SUMPRODUCT():

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Moreover, using COUNT() on a given range on your worksheet will only return a count of NUMBERS thereby excluding all the TEXT Values. This is the main reason lasw10 asked you to count the data in your J Range. If you use the function COUNTA() on J range, you could possibly see that you have TEXT Values in your column.

The values in the given range may not seem to be TEXT when viewed visually. The cells in the range may contain whitespaces, generally are TEXT characters which we are difficult to visualize or identify by our eyes.