SUM returns incorrect value with nested IF and VLOOKUP

[OS_dirk]

I am working on a spreadsheet to that has a list of items, and each item has its on weight (for example) and multiple possible options that that may or may not be present, in any combination, that would alter the weight of the final combination of item and options.

I have tried this both with and without out the use of the SUM formula, and in the current iteration of:

=$A4*SUM(
VLOOKUP($B$4, RT.$A$5:$AH$999, G$1, 0)
;IF($C4=“Y”, VLOOKUP($C$3, RT.$A$5:$AH$305, G$1, 0), 0)
;IF($D4=“Y”, VLOOKUP($D$3, RT.$A$5:$AH$305, G$1, 0), 0)
;IF(E$4=“Y”, VLOOKUP($E$3, RT.$A$5:$AH$305, G$1, 0), 0)
;IF(F$4=“Y”, VLOOKUP($F$3, RT.$A$5:$AH$305, G$1, 0), 0)
)

Item Count * VLOOKUP (Item Name, Range, Position) + If(Option One=“Yes”, VLOOKUP(Option Name, Range, Postion), Else 0) + If(Option Two=“Yes”, VLOOKUP(Option Name, Range, Postion), Else 0) + etc

Unfortunately, the results from the formula are intermittent.

I am able to have the “weight” value of the item added to the “weight” value of the options, but as soon as I move on to the next piece of data “length” (for example), it sums to correct value -1, and on the third piece of data “height” (for example) it ignores the additional value entirely. On the fourth piece of data “depth” (for example) it sums correctly.

The intermittent failure of LibreOffice to correctly SUM the results of the nested functions is extremely frustrating.

Other than the standard admonishments regarding negative numbers, decimals, and making sure the cells are set up for integers instead of text, what is the major malfunction?

How do I coax LibreOffice to handle this formula properly, preferable without having to start from scratch with a new database? I am attempting to create a concise spreadsheet with limited possibilities (using the Data Validity function), to quickly select items and options, and have it tally the resulting specifications from there without the end user having to do all of the research and calculations themselves.

Thanks in advance for any advice that can be offered.

[JohnSUN]

Sorry, but the description of the problem is not clear. You use VLOOKUP to search for the first encountered value of C3:F3 in the column RT.$A$5:$A$305 (or is it still $A$999?). Are you really only interested in the first value? Or is it necessary to sum up all the cells in the column with the number G$1, for which in the column A there is the desired value?

[OS_dirk]

Thanks, but unfortunately that error is from mistyping the formula as I copied it over for the original post.

Originally I was using a search range through $AH$999, but narrowed it down to 305 in case the breadth of the search window was causing memory issues. No such Luck.

[mikekaganski]

Are you sure you used absolute/relative addressing correctly? Your data looks inconsistent in IF condition cells:

=$A4*SUM(
        VLOOKUP($B$4, RT.$A$5:$AH$999, G$1, 0);
        IF($C4="Y", VLOOKUP($C$3, RT.$A$5:$AH$305, G$1, 0), 0);
        IF($D4="Y", VLOOKUP($D$3, RT.$A$5:$AH$305, G$1, 0), 0);
        IF(E$4="Y", VLOOKUP($E$3, RT.$A$5:$AH$305, G$1, 0), 0);
        IF(F$4="Y", VLOOKUP($F$3, RT.$A$5:$AH$305, G$1, 0), 0)
        )

See the $C4 and $D4 vs E$4 and F$4.