Combine 2 IF CONTAINS formulas searching 2 ranges
Needing some help.
I am running 2 sheets a master project sheet and an audit sheet populated off a form.
I have an IF CONTAINS formula that works to check a checkbox on the master project sheet when an audit is populated in the audit sheet with a unique number. =IF(CONTAINS(UN@row, {Audit Template Range 1}), 1, 0)
I want to in the master sheet check another check box when 2 criteria in that audit sheet are met on the same site. The unique number and review required. I tried the below and it check the checkbox but the second criteria does match Review Required
=IF(AND(CONTAINS(UN@row, {Audit Template Range 1}), CONTAINS("Review Required", {Audit Template Range 2})), 1, 0)
Best Answer
-
You would need a COUNTIFS instead.
=IF(COUNTIFS({Range 1}, CONTAINS(UN@row, @cell), {Range 2}, CONTAINS("Review Required", @cell))> 0, 1)
Answers
-
You would need a COUNTIFS instead.
=IF(COUNTIFS({Range 1}, CONTAINS(UN@row, @cell), {Range 2}, CONTAINS("Review Required", @cell))> 0, 1)
-
Thanks Paul that worked!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!