Can you use a CONTAINS formula within IF(AND statement?
Hi,
I have 2 sheets: 1 sheet is a grid of names and owed hours and the second sheet is a grid in which overtime is approved (A form is used to populate this)
I would like to highlight on the overtime sheet if someone has owed hours, This way overtime is not approved before the owed hours has been used.
As the overtime sheet is a form input the names do not always match in both the overtime and owed hours sheet, which is why I would like to implement the contains formula. I currently have this in the overtime sheet column to check the names between sheets:
=IF(CONTAINS([Full Name]@row, {Owed Hours Name}), "True", "False")
This part works no problem, However I am having trouble adding in extra criteria.
If the name match AND owed hours > 0 return amount of hours owed.
=IF(AND(CONTAINS([Full Name]@row, {Owed Hours Name}), {Amount Owed Hours} > 0), {Amount Owed Hours}, "")
Thank you for any feedback in advance!
Best Answer
-
Try using a SUMIFS instead.
=SUMIFS({Amount Owed Hours}, {Amount Owed Hours}, @cell> 0, {Owed Hours Name}, CONTAINS([Full Name]@row, @cell))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try using a SUMIFS instead.
=SUMIFS({Amount Owed Hours}, {Amount Owed Hours}, @cell> 0, {Owed Hours Name}, CONTAINS([Full Name]@row, @cell))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 211 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!