Formula to check if a cell contains "No" or isblank
This is probably so simple but I am struggling with my if and isblank, or maybe I don't need isblank and need just "" empty quotes. The Column title is "Added to CRM" and it can contain Yes, No, Revised or blank.
I would like to check a cell and if it contains either the word "No" or is blank return a phrase like "Open". If it contains any other word I don't care.
Help! Thank you.
Best Answer
-
Paul Newcome ✭✭✭✭✭✭
Try this:
=IF(OR([Added to CRM]@row = "No", [Added to CRM]@row = ""), "Open")
Answers
-
Paul Newcome ✭✭✭✭✭✭
Try this:
=IF(OR([Added to CRM]@row = "No", [Added to CRM]@row = ""), "Open")
-
Thank you! That worked. I was using IFBlank in the wrong way all I needed was a good old OR. Thanks again!
-
Paul Newcome ✭✭✭✭✭✭
-
K.J ✭
Hey@Paul Newcome, thank you for responding to this. I'd like to do the same however my original formula also checks for double up conflicts throughout the list. I use the red flag to show a conflict.
My original code snippet works for conflicts but when the cell is blank it also flags it as a conflict which makes sense.
=IF(COUNTIFS([vMix Metric]:[vMix Metric], @cell = [vMix Metric]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1)
I was attempting to add a ISBLANK in the hope I could clear up the blank cell conflict, but getting an error of INCORRECT ARGUMENT SET.
=IF(ISBLANK([vMix Metric]:[vMix Metric]), 0, IF(COUNTIFS([vMix Metric]:[vMix Metric], @cell = [vMix Metric]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1))
I would love some advice if possible! Thanks in advance.
-
Paul Newcome ✭✭✭✭✭✭
@K.JTry this:
=IF([vMix Metric]@row <> "",IF(COUNTIFS([vMix Metric]:[vMix Metric], @cell = [vMix Metric]@row, [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1))
-
K.J ✭
That's great, Paul, working well! Thank you very much. I've been stuck for a few weeks just tweaking the previous snippets.
There is an anomaly with one line where the dates don't overlap and it's being flagged, but it's only the one row that's flagging and not both which is how it worked in testing with your snippet. So, I'm not fussed about that.
-
Paul Newcome ✭✭✭✭✭✭
Glad it is mostly working for you, but even one issue means we need to check it out.
What happens if you apply a filter to the sheet to only show rows for that particular [vMix Metric]? Are you able to provide a screenshot of the results?
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/108408/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/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":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":54,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"