Countifs statement with date range

I am trying to write a countifs statement using two references on another sheet to count the number or projects not in "blue" status and with a due date between 1-3 months - right now I have:
=COUNTIFS({RAG}, <>"Blue" AND[{Date}, ">=Today()+31", {Date}, "<Today()+90"])
{RAG] is a reference on status to another sheet and
{Date} is reference to due date from another sheet
I keep getting error message.
Thanks for the help!
Best Answer
-
Hi @ColleenU
A COUNTIFS statement has the "and" built into it, so you just need commas between your ranges and criteria without the AND function. If you did use an AND in other formulas, keep in mind that functions use round parentheses like this: AND(...) versus AND[...]
I see also that you're using the TODAY function, which doesn't need to be in quotes (since it's a function, not text). You can add a number (in these) for the TODAY function to reference a specific number of days from today: TODAY(31)
With all these small updates, let's try your formula again!
=COUNTIFS({RAG}, <> "Blue", {Date}, >= TODAY(31), {Date}, < TODAY(90))
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 @ColleenU
A COUNTIFS statement has the "and" built into it, so you just need commas between your ranges and criteria without the AND function. If you did use an AND in other formulas, keep in mind that functions use round parentheses like this: AND(...) versus AND[...]
I see also that you're using the TODAY function, which doesn't need to be in quotes (since it's a function, not text). You can add a number (in these) for the TODAY function to reference a specific number of days from today: TODAY(31)
With all these small updates, let's try your formula again!
=COUNTIFS({RAG}, <> "Blue", {Date}, >= TODAY(31), {Date}, < TODAY(90))
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
-
Thank you so much Genevieve - it worked - I realize also I didn't have the word "today" in caps - so with your help, I got it to work!
-
Well-done! π Glad I could help.
Need more information? π | Help and Learning Center
γγγ«γ‘γ― (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!π | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!