30 Days or more + Every status except for one
I am looking for a count of rows that have a date before the last 30 days [Last Date of BHL Contact], and the status in [BHL Appointment Follow Up Status] is anything except for “Appointment Attendance Confirmed”
I have tried many variations of =COUNTIFS([Last Date of BHL Contact]:[Last Date of BHL Contact], >=TODAY(-30)), … such as
- NOT(CONTAINS(“Appointment Attendance Confirmed”, [BHL Appointment Follow Up Status]@row))
- [BHL Appointment Follow Up Status]:[BHL Appointment Follow Up Status], <>“Appointment Attendance Confirmed”)
And I keep coming up short. Please help.
Thanks,
Alix
Best Answer
-
Hi Alix,
It sounds like you're trying to count rows in Smartsheet where:
- The [Last Date of BHL Contact] column has a date before 30 days ago.
- The [BHL Appointment Follow Up Status] column does not contain the value "Appointment Attendance Confirmed."
Here’s how you can approach it:
Correct
COUNTIFS
Formula:=COUNTIFS([Last Date of BHL Contact]:[Last Date of BHL Contact], <TODAY(-30), [BHL Appointment Follow Up Status]:[BHL Appointment Follow Up Status], <> "Appointment Attendance Confirmed")
Key Points:
- Date Condition: Use
<TODAY(-30)
for dates before 30 days ago. Make sure the column format for [Last Date of BHL Contact] is set to "Date." - Excluding a Value: Use
<> "Appointment Attendance Confirmed"
to exclude rows where the [BHL Appointment Follow Up Status] column equals this value.
Common Troubleshooting Tips:
- Column Names: Ensure the column names are spelled exactly as they appear in your sheet, including spaces and capitalization.
- Data Types: Verify that:
- The [Last Date of BHL Contact] column is formatted as "Date."
- The [BHL Appointment Follow Up Status] column is formatted as "Text/Number."
- Parentheses and Syntax: Ensure proper use of parentheses and syntax, as Smartsheet is strict about them.
If This Formula Fails:
Smartsheet sometimes struggles with combining logical exclusions (
<>
) and functions likeTODAY()
. A workaround is to use a helper column:Helper Column Approach:
- =IF(AND([Last Date of BHL Contact]@row < TODAY(-30), [BHL Appointment Follow Up Status]@row <> "Appointment Attendance Confirmed"), 1, 0)
- =SUM([Include in Count]:[Include in Count])
This approach ensures row-level evaluation is performed correctly.
Let me know if you need more assistance!
Ask Me About Smartsheet Maps?!?!
Solving Automation, Integration, & Adoption Problems For Smartsheet Customers
Account Executive | Skyway Consulting Co
Answers
-
Hi Alix,
It sounds like you're trying to count rows in Smartsheet where:
- The [Last Date of BHL Contact] column has a date before 30 days ago.
- The [BHL Appointment Follow Up Status] column does not contain the value "Appointment Attendance Confirmed."
Here’s how you can approach it:
Correct
COUNTIFS
Formula:=COUNTIFS([Last Date of BHL Contact]:[Last Date of BHL Contact], <TODAY(-30), [BHL Appointment Follow Up Status]:[BHL Appointment Follow Up Status], <> "Appointment Attendance Confirmed")
Key Points:
- Date Condition: Use
<TODAY(-30)
for dates before 30 days ago. Make sure the column format for [Last Date of BHL Contact] is set to "Date." - Excluding a Value: Use
<> "Appointment Attendance Confirmed"
to exclude rows where the [BHL Appointment Follow Up Status] column equals this value.
Common Troubleshooting Tips:
- Column Names: Ensure the column names are spelled exactly as they appear in your sheet, including spaces and capitalization.
- Data Types: Verify that:
- The [Last Date of BHL Contact] column is formatted as "Date."
- The [BHL Appointment Follow Up Status] column is formatted as "Text/Number."
- Parentheses and Syntax: Ensure proper use of parentheses and syntax, as Smartsheet is strict about them.
If This Formula Fails:
Smartsheet sometimes struggles with combining logical exclusions (
<>
) and functions likeTODAY()
. A workaround is to use a helper column:Helper Column Approach:
- =IF(AND([Last Date of BHL Contact]@row < TODAY(-30), [BHL Appointment Follow Up Status]@row <> "Appointment Attendance Confirmed"), 1, 0)
- =SUM([Include in Count]:[Include in Count])
This approach ensures row-level evaluation is performed correctly.
Let me know if you need more assistance!
Ask Me About Smartsheet Maps?!?!
Solving Automation, Integration, & Adoption Problems For Smartsheet Customers
Account Executive | Skyway Consulting Co
-
Great, thanks for your prompt reply and your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!