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

Joseph M

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.

Joseph M

After checking above the error #no match still exists

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))