Help with multiple IFContains Statements
Hi there,
I'm trying to do a formula but it doesn't seem to be working. I basically want to put a Yes or No in a sheet if 2 conditions are met - if one column of another sheet contains the age group 20-30 AND if another column of that sheet contains a code.
=IF(CONTAINS("20-30", {NELFT Talking Therapies - Message Request Range 3}), "Yes", "No") AND (IF(CONTAINS([ODS Code]@row,{NELFT Talking Therapies - Message Request Range 2}),"Yes","No"))
Could you please help?
Best Answer
-
@Tara Bush Great! Glad it worked for you. :) Have a great weekend!
Answers
-
Hi @Tara Bush ,
Just to clarify - are you checking whether the two conditions are met in the same row in the referenced sheet, or that they exist separately somewhere in the sheet? Also, is there a reason you're using the CONTAINS function? If the cells are going to be an exact match (as in, they will only contain "20-30" in the cell or only the same contents as [ODS Code]@row), then there's no reason to fiddle with CONTAINS.
Let me know and we can map it out.
Best,
Heather
-
Hi @Heather Duff - Yes that's right, they are in the same row in the referenced sheet/
I chose contains because the 'ODS Code' is not an exact match - it's concatenated with a name, e.g. Surgery-ODS Code. However, the 20-30 would be an exact match,.
Hope that makes sense!
-
Ok! Let's try this:
=IF(COUNT(COLLECT({NELFT Talking Therapies - Message Request Range 3},{NELFT Talking Therapies - Message Request Range 3},"20-30",{NELFT Talking Therapies - Message Request Range 2},CONTAINS([ODS Code]@row,@cell)))>0,"Yes","No")
This translates to:
If the number of rows that meet these two conditions (Range 3 is equal to "20-30" and Range 2 contains the ODS Code in this row) is greater than zero, show Yes; otherwise, show No.
Let me know if it works for you.
Best,
Heather
-
Thanks for this. Unfortunately the formula returns 'Yes' for all values, even if the codes aren't in the referenced sheet.
-
Hmm...Let's try again, then.
=IF(COUNTIFS({NELFT Talking Therapies - Message Request Range 3},"20-30",{NELFT Talking Therapies - Message Request Range 2},CONTAINS([ODS Code]@row,@cell))>0,"Yes","No")
-
Sorry @Heather Duff ignore me - when I edit the referenced sheet and re-add them, it works!!
Thanks so much!
-
@Tara Bush Great! Glad it worked for you. :) Have a great weekend!
-
Thanks so much - you too!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!