am trying to count the number of inspections approved within a range of date formula not working
Answers
-
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:
- 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. - 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 useDATE(year, month, day)
format. - Comparison Operators: Place the comparison operators (
>=
and<=
) directly before theDATE
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.
bassam.khalil2009@gmail.com
☑️ 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"
- Quotation Marks: Make sure you use standard quotation marks. In your formula, the quotation marks around
-
After checking above the error #no match still exists
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!