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))
Answers
-
Try using a SUMIFS instead.
=SUMIFS({Amount Owed Hours}, {Amount Owed Hours}, @cell> 0, {Owed Hours Name}, CONTAINS([Full Name]@row, @cell))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!