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

  • Hunter Taylor
    Hunter Taylor ✭✭✭
    Answer ✓

    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!

    Ask Me About Smartsheet Maps?!?!

    Solving Automation, Integration, & Adoption Problems For Smartsheet Customers

    Account Executive | Skyway Consulting Co

    Explore The Possibilities of Smartsheet & ArcGIS

Answers

  • Hunter Taylor
    Hunter Taylor ✭✭✭
    Answer ✓

    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!

    Ask Me About Smartsheet Maps?!?!

    Solving Automation, Integration, & Adoption Problems For Smartsheet Customers

    Account Executive | Skyway Consulting Co

    Explore The Possibilities of Smartsheet & ArcGIS

  • AlixGustav
    AlixGustav ✭✭✭

    Great, thanks for your prompt reply and your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!