COUNTIF using Less than Today
I am struggling with a formula that is trying to count the Record Type entries in the reference sheet that meet the following conditions:
Record Type = "TEST"
Deadline is less than TODAY
Assigned ISBLANK
I tried many variations of the following formula but it keeps erroring out with INVALID OPERATION. When I parse it out, it works until I add the less than today criteria at the end.
=COUNTIFS({Record Type}, {Record Type} = "TEST", {Assigned}, ISBLANK(@cell), {Deadline} < TODAY())
Note: all conditions are in the same reference sheet and I confirmed that the {Deadline} column is a Date type.
TIA!
Best Answer
-
Hi @Diane Moore
The way a COUNTIFS works is that you list the range or the column, then a comma, then the criteria. It looks like you have all the building blocks correct, we just need to adjust the formatting!
Try this:
=COUNTIFS({Record Type}, @cell = "TEST", {Assigned}, ISBLANK(@cell), {Deadline}, @cell < TODAY())
In your criteria, I used @cell to indicate that in the previously stated range, we want to check each cell for the criteria of "Test" or TODAY.
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @Diane Moore
The way a COUNTIFS works is that you list the range or the column, then a comma, then the criteria. It looks like you have all the building blocks correct, we just need to adjust the formatting!
Try this:
=COUNTIFS({Record Type}, @cell = "TEST", {Assigned}, ISBLANK(@cell), {Deadline}, @cell < TODAY())
In your criteria, I used @cell to indicate that in the previously stated range, we want to check each cell for the criteria of "Test" or TODAY.
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. Thank you. This worked perfectly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!