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)
Check a Box if a Cell Contains Text and is Between Two Dates
![ElizabethOwen](https://us.v-cdn.net/6031209/uploads/avatarstock/nB5UV6VYA5H58.png)
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
-
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 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.
-
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
-
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)) -
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!