# Formula returns #VALUE! for some cells

#### Neales

##### New Member
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 -->

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Guanjin Peter

##### Active Member
if the returned value is a number....I would suggest a sumProduct formula...it's crazily huge!

#### Neales

##### New Member
Yeah it is quite a long formula. It has a lot of criteria to look at though to return the correct value. There is a sumproduct in there and thats where the problem appears to be.

#### Stormseed

##### Banned
Hi Neales

Welcome to MrExcel.

I do see that you have a huge formula in your worksheet which rather makes it very difficult and complex for the users and members to read & understand the same.

I would suggest you to try rectifying your formula by breaking it into smaller fragments. Try to calculate each and every part of the formula individually and probably you will get to know where the problem actually exists in your formula. Once you find out the problem and rectify it, you can then merge the formulas together again to accomplish your need.

Do this and even if you do not succeed, you can always post your question on this board clarifying what you need to accomplish in Excel. In other words, your question with full details and your desired answer.

#### Neales

##### New Member

I have done what you have suggested already. I know the problem with the formula is from the "/" onwards.

If I delete the part I highlighted in bold from my first post the value that I wish to be divided is displayed. So the problem is that the forumla wont calculate the figure to divide this by.

To do that the formula has to match several criteria to find a value and then multiply this by 4.33. Then this figure should be used to divide the value given by the formula before this point.

So if I give you all an example.

Cell U23 with the current formula displays #VALUE!. If I use the formula before the "/" which would be

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

then the value 220.18 is returned. Now the last part of the formula missing from that above looks at certain criteria to to return a figure that can be multiplied by 4.33. The formula then divides the original value of 220.18 by the latter value to give the answer.

#### Stormseed

##### Banned

Perhaps, you could explain it in more detail about the issue that you are experiencing ? We can take a broader look at your problem #### DonkeyOte

##### MrExcel MVP
have you tried encasing the sumproduct prior to multiplication by 4.33 ?

#### Neales

##### New Member
Yeah I've tried that #### DonkeyOte

##### MrExcel MVP
the only distinction between numerator and denominator is the summation column, correct (J) ? All other denominator columns referenced are also referenced in the numerator and if the numerator works correctly then it follows that the denominator equivalents will also work correctly so thefore the issue must be in J, no ? Are all your values in J range numeric ? Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,331
Messages
5,836,683
Members
430,444
Latest member
WrenchBoy ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back