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
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
-
@Genevieve P. Thank you. This worked perfectly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!