If(Contains) issue

Trevor O.

I am trying to look at a column on one sheet and if it contains a certain value or multiple of the values that I am searching for, return a sum of value(s) from one or more specific cells that are contained in a separate sheet, otherwise return as 0 or as if nothing has happened. This is the formula I have come up with but it returns as #Unparseable and I do not understand why...

=IF(([Customer Order Qty]@row - [# of Current Inventory]@row) * 1.05 - [Qty in Process]@row - IF(CONTAINS("Flex 3260", {Current Orders Range 3}), [# of Current Inventory]4, 0) - IF(CONTAINS("Flex 3360", {Current Orders Range 3}), [# of Current Inventory]5, 0) - IF(CONTAINS("Flex 3365", {Current Orders Range 3}), [# of Current Inventory]6, 0) - IF(CONTAINS("Access 5360",{Current Orders Range 3}), [# of Current Inventory]7, 0) - IF(CONTAINS("Flex 3362",{Current Orders Range 3}) [# of Current Inventory]8, 0) < 0, 0, ([Customer Order Qty]@row - [# of Current Inventory]@row) * 1.05 - [Qty in Process]@row - IF(CONTAINS("Flex 3260", {Current Orders Range 3}), [# of Current Inventory]4, 0) - IF(CONTAINS("Flex 3360", {Current Orders Range 3}), [# of Current Inventory]5, 0) - IF(CONTAINS("Flex 3365", {Current Orders Range 3}), [# of Current Inventory]6, 0) - IF(CONTAINS("Access 5360",{Current Orders Range 3}), [# of Current Inventory]7, 0) - IF(CONTAINS("Flex 3362",{Current Orders Range 3}) [# of Current Inventory]8, 0))

Any ideas why?

Dakota Haeffner

@Trevor O.

Hey Trevor,

Nothing stands out obviously to me but what I typically do with unparseables is throw them into a AI tool to see if I missed a comma or a parentheses. Mircrosoft's CoPilot didn't seem to find anything, but I usually have better luck with ChatGPT, unfortunately, it's blocked on this computer. Here is the response from CoPilot,

1. Syntax Consistency: Ensure that the formula follows the correct syntax for your spreadsheet software (e.g., Excel, Google Sheets). Make sure all parentheses, commas, and operators are correctly placed.
2. Data References: Verify that the cell references ([Customer Order Qty]@row, [# of Current Inventory]@row, etc.) point to valid cells in your sheet. If any of these references are incorrect, it can lead to an unparseable error.
3. Function Arguments: Check the arguments within the IF functions. Ensure that they match the expected format. For example, if you’re using CONTAINS, make sure the text and range references are accurate.
4. Numeric Values: Confirm that numeric values (e.g., 1.05) are correctly formatted. Avoid any extraneous characters or spaces.
5. Logical Conditions: The condition within the outer IF statement should evaluate to either TRUE or FALSE. Double-check that the logic is correct.

Trevor O.

@Dakota Haeffner

Thank you for your response. I had to look at my formula piece by piece and eventually found out I was missing one singular comma. Amazing how that little mistake can corrupt an entire formula but I'm glad I was able to find it using your suggestion and the formula works now!