30 Days or more + Every status except for one

AlixGustav

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

Hunter Taylor

Hi Alix,

It sounds like you're trying to count rows in Smartsheet where:

1. The [Last Date of BHL Contact] column has a date before 30 days ago.
2. 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:

1. 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."
2. Excluding a Value: Use <> "Appointment Attendance Confirmed" to exclude rows where the [BHL Appointment Follow Up Status] column equals this value.

Common Troubleshooting Tips:

1. Column Names: Ensure the column names are spelled exactly as they appear in your sheet, including spaces and capitalization.
2. 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."
3. 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 like TODAY(). A workaround is to use a helper column:

Helper Column Approach:

1. =IF(AND([Last Date of BHL Contact]@row < TODAY(-30), [BHL Appointment Follow Up Status]@row <> "Appointment Attendance Confirmed"), 1, 0)
2. =SUM([Include in Count]:[Include in Count])

This approach ensures row-level evaluation is performed correctly.

Let me know if you need more assistance!

Hunter Taylor

Hi Alix,

It sounds like you're trying to count rows in Smartsheet where:

1. The [Last Date of BHL Contact] column has a date before 30 days ago.
2. 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:

1. 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."
2. Excluding a Value: Use <> "Appointment Attendance Confirmed" to exclude rows where the [BHL Appointment Follow Up Status] column equals this value.

Common Troubleshooting Tips:

1. Column Names: Ensure the column names are spelled exactly as they appear in your sheet, including spaces and capitalization.
2. 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."
3. 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 like TODAY(). A workaround is to use a helper column:

Helper Column Approach:

1. =IF(AND([Last Date of BHL Contact]@row < TODAY(-30), [BHL Appointment Follow Up Status]@row <> "Appointment Attendance Confirmed"), 1, 0)
2. =SUM([Include in Count]:[Include in Count])

This approach ensures row-level evaluation is performed correctly.

Let me know if you need more assistance!

AlixGustav

Great, thanks for your prompt reply and your help!