IF Contains Nested Formula Error
Hello!
Would anybody be able to see what I'm doing wrong here? I'm trying to make it so if my "Part Number" column contains "Purchase Order" that the green icon doesn't show up.
Here's my formula:
=如果(Status@row =“供应商”,“”,如果(Status@row = "秩序ed", "", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "", IF(Status@row = "Hold", "", IF(Status@row = "Cancelled", "", IF(Status@row = "Order Confirmed", "", IF(Status@row = "Reference Only", "", IF(Status@row = "", "", IF(CONTAINS("Purchase Order", [Part Number]:[Part Number]), "")))))))))))))
I'm just trying to make the formatting look nicer. Also if I filter for the green circle in a report I don't want to see those rows.
Best Answer
-
Paul Newcome ✭✭✭✭✭✭@AnnieRNot quite. Each CONTAINS function should be closed independently. This means that one of your bold closing parenthesis should be moved to close out the "Credit Card" CONTAINS.
=如果(Status@row =“供应商”,“”,如果(Status@row = "秩序ed", "", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "", IF(Status@row = "Hold", "", IF(Status@row = "Cancelled", "", IF(Status@row = "Order Confirmed", "", IF(Status@row = "Reference Only", "", IF(OR(CONTAINS("Purchase Order", [Part Number]@row), CONTAINS("Credit Card", [Part Number]@row),CONTAINS("Stock Items", [Part Number]@row)),"", IF(Status@row = "", ""))))))))))))
Think of it this way:
OR(argument1, argument2, argument3)
CONTAINS("A", Column@row)
CONTAINS("B", Column@row)
CONTAINS("C", Column@row)
You would then drop each of the CONTAINS functions into an "argument" section of the OR function like so:
OR(CONTAINS("A", Column@row), CONTAINS("B", Column@row), CONTAINS("C", Column@row))
Answers
-
Paul Newcome ✭✭✭✭✭✭
Try changing it from a column reference
[Part Number]:[Part Number]
to a cell reference the same way you referenced your Status
[Part Number]@row
-
AnnieR ✭Thank you@Paul Newcome! Unfortunately that didn't work.
-
Paul Newcome ✭✭✭✭✭✭
Are you able to provide the exact (new) formula as well as a screenshot of a row containing "Purchase Order" in the [Part Number] column?
-
AnnieR ✭
Yes! :)
=如果(Status@row =“供应商”,“”,如果(Status@row = "秩序ed", "", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "", IF(Status@row = "Hold", "", IF(Status@row = "Cancelled", "", IF(Status@row = "Order Confirmed", "", IF(Status@row = "Reference Only", "", IF(Status@row = "", "", IF(CONTAINS("Purchase Order", [Part Number]@row), "")))))))))))))
-
Paul Newcome ✭✭✭✭✭✭
Oh. Your argument immediately before is to output the Green Ball if the Status cell is empty. Try switching those two around so the IF(CONTAINS(.....), "", portion comes before the Status is blank portion.
-
AnnieR ✭
Darnit sorry@Paul NewcomeI don't know how I'm mixing this up. I also need to add in to be blank if "Credit Card" is in the Part Number column but I can't even get this to work.
=如果(Status@row =“供应商”,“”,如果(Status@row = "秩序ed", "", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "", IF(Status@row = "Hold", "", IF(Status@row = "Cancelled", "", IF(Status@row = "Order Confirmed", "", IF(Status@row = "Reference Only", "", IF(CONTAINS("Purchase Order", [Part Number]@row), " "), IF(Status@row = "", ""))))))))))))
Now I'm just getting an Incorrect Arguement Error.
-
Paul Newcome ✭✭✭✭✭✭
The error message is coming from a misplaced closing parenthesis before the final IF. Here it is removed.
=如果(Status@row =“供应商”,“”,如果(Status@row = "秩序ed", "", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "", IF(Status@row = "Hold", "", IF(Status@row = "Cancelled", "", IF(Status@row = "Order Confirmed", "", IF(Status@row = "Reference Only", "", IF(CONTAINS("Purchase Order", [Part Number]@row), "", IF(Status@row = "", ""))))))))))))
To factor in the "Credit Card" bit, we would add an OR statement to the blank output like so:
=如果(Status@row =“供应商”,“”,如果(Status@row = "秩序ed", "", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "", IF(Status@row = "Hold", "", IF(Status@row = "Cancelled", "", IF(Status@row = "Order Confirmed", "", IF(Status@row = "Reference Only", "", IF(OR(CONTAINS("Purchase Order", [Part Number]@row), CONTAINS("Credit Card", [Part Number]@row)), "", IF(Status@row = "", ""))))))))))))
-
AnnieR ✭
@Paul NewcomeThank you!!!! That worked! The only weird thing was it didn't work if I just copy pasted it in. I had to hand key it back in, which I thought was pretty odd. It was giving me a syntax error if I just copy pasted.
If I wanted to add in a few more items for the "Purchase Order" or "Credit Card" bits. Like "Stock Items" would this be right?
=如果(Status@row =“供应商”,“”,如果(Status@row = "秩序ed", "", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "", IF(Status@row = "Hold", "", IF(Status@row = "Cancelled", "", IF(Status@row = "Order Confirmed", "", IF(Status@row = "Reference Only", "", IF(OR(CONTAINS("Purchase Order", [Part Number]@row), CONTAINS("Credit Card", [Part Number]@row, CONTAINS("Stock Items", [Part Number]@row))), "", IF(Status@row = "", ""))))))))))))
-
Paul Newcome ✭✭✭✭✭✭
@AnnieRNot quite. Each CONTAINS function should be closed independently. This means that one of your bold closing parenthesis should be moved to close out the "Credit Card" CONTAINS.
=如果(Status@row =“供应商”,“”,如果(Status@row = "秩序ed", "", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "", IF(Status@row = "Hold", "", IF(Status@row = "Cancelled", "", IF(Status@row = "Order Confirmed", "", IF(Status@row = "Reference Only", "", IF(OR(CONTAINS("Purchase Order", [Part Number]@row), CONTAINS("Credit Card", [Part Number]@row),CONTAINS("Stock Items", [Part Number]@row)),"", IF(Status@row = "", ""))))))))))))
Think of it this way:
OR(argument1, argument2, argument3)
CONTAINS("A", Column@row)
CONTAINS("B", Column@row)
CONTAINS("C", Column@row)
You would then drop each of the CONTAINS functions into an "argument" section of the OR function like so:
OR(CONTAINS("A", Column@row), CONTAINS("B", Column@row), CONTAINS("C", Column@row))
-
AnnieR ✭
I'm sorry for my late reply@Paul Newcome!! This worked perfectly!! Thank you for teaching me too. I really appreciate it. Happy Friday and hope you have a good weekend :)
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
HAS() will only return true under three conditions.<\/p>
- The cell is a multicontact cell and contains a match for the contact<\/li>
- The cell is a multiselect dropdown and contains a match<\/li>
- The cell is not a multicontact or multiselect dropdown and contains ONLY and EXACTLY the text you are matching against. <\/li><\/ol>
I am assuming your row in question is a standard Text\/Number cell? If so, you will need to get somewhat creative with some CONTAINS() statements.<\/p>
This will narrow down your matches to only cells that contain a space both before and after TAC. If, however, TAC can appear as the first or last \"word\" in the cell, it would not match in that circumstance as there would only be a space on one side.<\/p>
=IF(CONTAINS(\" TAC \", [Column Name1]@row), 1, 0)<\/p>
<\/p>If TAC can appear as the first or last \"word\" in the cell, this will cover those possibilities as well.<\/p>
=IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\"), 1, 0)<\/p>
<\/p>If it is possible that TAC will be the exact entry of the cell, you will need an additional statement to cover that, as there would not be spaces on either side. This will add that option.<\/p>
=IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\", [Column Name1]@row = \"TAC\"), 1, 0)<\/p>
<\/p>If there are other situations you may have in your sheet, i.e., TAC inside parenthesis, or before or after punctuation, etc, you will additional statements to include those options as well.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":109170,"type":"question","name":"COUNTIFS formula with 3 criteria (2 of the criteria have formulas)","excerpt":"Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another dropdown of project names to select from), and Severity (this column displays a \"High\", \"Medium\", or \"Low\" value based on the calculated score in column…","snippet":"Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another…","categoryID":322,"dateInserted":"2023-08-18T20:29:26+00:00","dateUpdated":null,"dateLastComment":"2023-08-18T21:13:12+00:00","insertUserID":165367,"insertUser":{"userID":165367,"name":"Shari D","url":"https:\/\/community.smartsheet.com\/profile\/Shari%20D","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-18T21:12:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":93,"score":null,"hot":3384789758,"url":"https:\/\/community.smartsheet.com\/discussion\/109170\/countifs-formula-with-3-criteria-2-of-the-criteria-have-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109170\/countifs-formula-with-3-criteria-2-of-the-criteria-have-formulas","format":"Rich","lastPost":{"discussionID":109170,"commentID":391505,"name":"Re: COUNTIFS formula with 3 criteria (2 of the criteria have formulas)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391505#Comment_391505","dateInserted":"2023-08-18T21:13:12+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-18T21:12:26+00:00","dateAnswered":"2023-08-18T21:01:48+00:00","acceptedAnswers":[{"commentID":391499,"body":"
