Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • 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

  • 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

  • ✭✭✭✭

    Hey,

    Try below and let me know if this works!

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

  • ✭✭✭✭

    It came back with #UNPARSEABLE. I will note that the {Case Subjects} may contain the [Customer Name]@row but there could be other customer names in the cell, too.

  • 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

  • ✭✭✭✭

    You're so right! Thanks for the correction @DKazatsky2

  • Community Champion

    Hi @ElizabethOwen,

    I meant to tag you on the above.

  • ✭✭✭✭

    I tried this one as well, and unfortunately, I got the #INCORRECT ARGUMENT SET. Started it from scratch on a new row to make sure it wasn't a glitch. It feels like it should be working so I am not sure what's going wrong.

    =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))

  • 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)

  • ✭✭✭✭

    Thank you!! Perfect!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions