Can you use a CONTAINS formula within IF(AND statement?

Jordan Norris

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!

Paul Newcome

Try using a SUMIFS instead.

=SUMIFS({Amount Owed Hours}, {Amount Owed Hours}, @cell> 0, {Owed Hours Name}, CONTAINS([Full Name]@row, @cell))

Paul Newcome

Try using a SUMIFS instead.

=SUMIFS({Amount Owed Hours}, {Amount Owed Hours}, @cell> 0, {Owed Hours Name}, CONTAINS([Full Name]@row, @cell))