# If(Contains) issue

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?

• ✭✭✭✭✭

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.

• 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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!