I can't get the AND function to work if the conditions are met. Can anyone help?

Hello,

I'm trying to have a number value show up in all 3 conditions are met, but the function won't work.

I only want the number value to show up if the the date falls between the start and end of the project period and if a checkbox is also ticked. I've written this so far.

=IF((IF(AND([End Date]@row >= [Week 1]@row, [Start Date]@row <= [Week 1]@row, [M1]@row = "1"), "True", "False")) = "True", [Count # of Workers]@row, 0)

The problem seems to come from the checkmark box column [M1]. The AND function works if I remove it so that it reads

=IF((IF(AND([End Date]@row >= [Week 1]@row, [Start Date]@row <= [Week 1]@row), "True", "False")) = "True", [Count # of Workers]@row, 0)

It also works when I run the [M1]@row = 1 function alone. However, when I add all three conditions together then the function doesn't seem to work properly. Does anyone have any work arounds?

=IF((IF(AND([End Date]@row >= [Week 1]@row, [Start Date]@row <= [Week 1]@row, [M1]@row = "1"), "True", "False")) = "True", [Count # of Workers]@row, 0)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!