Formula to show the number of items that meet 2 criteria
I am looking to tally up the number of tasks that meet the following criteria:
- Don't have a check in the column "Done"
- Have a due date within the next 14 days
I am actually going to create 3 buckets (due within the next 14 days from today (whenever it's opened), due between 15 and 30 days and due in over 30 days).
I tried this formula but it's "#UNPARSEABLE". I tried using @row instead of selecting rows 1 - 36 but was having issues with that so I just picked a range for testing purposes.
Any tips where I am going wrong?
=COUNTIFS(Done1:Done36, =0, ([Due Date]1:[Due Date]36, <=TODAY(14))))
Best Answer
-
Try
=COUNTIFS(Done1:Done36, 0, [Due Date]1:[Due Date]36, <=TODAY(14),[Due Date]1:[Due Date]36, >=TODAY())
You do not need the equals and the brackets around the second condition
You could add greater than equal to Today() so as to not include previous dates, also if you want to have the range as the whole columns remove the row numbers
=COUNTIFS(Done:Done, 0, [Due Date]:[Due Date], <=TODAY(14),[Due Date]:[Due Date], >=TODAY())
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Answers
-
Try
=COUNTIFS(Done1:Done36, 0, [Due Date]1:[Due Date]36, <=TODAY(14),[Due Date]1:[Due Date]36, >=TODAY())
You do not need the equals and the brackets around the second condition
You could add greater than equal to Today() so as to not include previous dates, also if you want to have the range as the whole columns remove the row numbers
=COUNTIFS(Done:Done, 0, [Due Date]:[Due Date], <=TODAY(14),[Due Date]:[Due Date], >=TODAY())
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
Worked great! Thanks for your help Frank!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!