Check a Box if a Cell Contains Text and is Between Two Dates

Hey there,

I am trying to get the box on my main sheet (Sheet 1) to check if the "Case Subjects" cell on Sheet 2 contains the "Customer Name" on Sheet 1 AND the "Close Date" on Sheet 2 is between two dates. This is what I have so far but it's not working. At the moment it's not returning an error, but it's not checking off the box when it should.

=IF(COUNTIFS({EDD Closed Date}, >=DATE(2024, 12, 1), {EDD Closed Date}, <=DATE(2024, 12, 31), {Case Subjects}, "*" + [Customer Name]@row + "*") > 0, 1, 0)

Best Answers

  • DKazatsky2
    DKazatsky2 Community Champion
    Answer ✓

    Hi @alyssad,

    Add a "CONTAINS" into your formula and it should do the trick.

    =IF(COUNTIFS({EDD Closed Date}, >=DATE(2024, 12, 1), {EDD Closed Date}, <=DATE(2024, 12, 31), {Case Subjects}, CONTAINS([Customer Name]@row, @cell) > 0, 1)

    Hope this helps,

    Dave

  • DKazatsky2
    DKazatsky2 Community Champion
    Answer ✓

    @ElizabethOwen,

    You have one of the closing ")" in the wrong place, it should look like the following.

    =IF(COUNTIFS({EDD Closed Date}, >=DATE(2024, 12, 1), {EDD Closed Date}, <=DATE(2024, 12, 31), {Case Subjects}, CONTAINS([Customer Name]@row, @cell)) > 0, 1)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!