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?
Answers

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,
 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.
 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.  Function Arguments: Check the arguments within the
IF
functions. Ensure that they match the expected format. For example, if you’re usingCONTAINS
, make sure the text and range references are accurate.  Numeric Values: Confirm that numeric values (e.g.,
1.05
) are correctly formatted. Avoid any extraneous characters or spaces.  Logical Conditions: The condition within the outer
IF
statement should evaluate to eitherTRUE
orFALSE
. Doublecheck 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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 210 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 300 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!