am trying to count the number of inspections approved within a range of date formula not working



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Joseph M,

    It looks like there's a slight syntax error in your formula, particularly around the use of quotation marks and the Date function. Here's a corrected version of the formula to count the number of inspections approved within a date range (from January 30, 2024, to February 13, 2024) in Smartsheet:

    =COUNTIFS([Approval Status]19:[Approval Status]268, "Approved", [Sorted Date]19:[Sorted Date]268,
     >=DATE(2024, 01, 30), [Sorted Date]19:[Sorted Date]268, <=DATE(2024, 02, 13))

    Here are the key points to ensure the formula works correctly:

    1. Quotation Marks: Make sure you use standard quotation marks. In your formula, the quotation marks around "Approved" seem to be non-standard or typographic (curly quotes). This can happen when copying and pasting from certain text editors. Use straight quotation marks instead.
    2. DATE Function: Ensure that the DATE function is used correctly with proper syntax. It looks like it was correct in your formula, but just reiterating to use DATE(year, month, day) format.
    3. Comparison Operators: Place the comparison operators (>= and <=) directly before the DATE function without a comma separating them from the range. The way you've placed them looks correct, but ensure there's no extra space or characters that might break the formula.

    If after these adjustments your formula still doesn't work, consider the following troubleshooting tips:

    • Column Names and Ranges: Double-check the column names ([Approval Status] and [Sorted Date]) and ranges (19:268) to ensure they match your sheet's layout.
    • Data Types: Confirm that the data in the [Sorted Date] column is formatted as dates and the [Approval Status] column contains the exact text "Approved" (case-sensitive) where expected.
    • Smartsheet Locale: Smartsheet formulas can be sensitive to locale settings, particularly date formats. Ensure your Smartsheet account settings match the date format you're using in the DATE function.

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Joseph M

    After checking above the error #no match still exists

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There are no syntax issues within the original screenshot formula. Have you tried breaking it down to see if there is one piece in particular holding things up?

    =COUNTIFS([Approval Status]19:[Approval Status]268, @cell = "Approved")

    =COUNTIFS([Sorted Date]19:[Sorted Date]268, @cell>= DATE(2024, 01, 30))

    =COUNTIFS([Sorted Date]19:[Sorted Date]268, @cell<= DATE(2024, 02, 13))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!