Countifs statement with date range

ColleenU

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!

Genevieve P.

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

Genevieve P.

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

ColleenU

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!

Genevieve P.

Well-done! 🎉 Glad I could help.