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