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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
@Genevieve P. Thank you. This worked perfectly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!