Countifs statement with date range

ColleenU
ColleenU ✭✭
edited 07/01/22 in Formulas and Functions

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

  • Genevieve P.
    Genevieve P. Employee
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!