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)

Find more posts tagged with

Accepted answers

DKazatsky2

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

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

All comments

alyssad

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)

ElizabethOwen

https://community.smartsheet.com/discussion/comment/471455#Comment_471455

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.

DKazatsky2

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

alyssad

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

DKazatsky2

Hi @ElizabethOwen,

I meant to tag you on the above.

ElizabethOwen

https://community.smartsheet.com/discussion/comment/471688#Comment_471688

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

DKazatsky2

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

ElizabethOwen

https://community.smartsheet.com/discussion/comment/471873#Comment_471873

Thank you!! Perfect!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.