# Countifs statement with date range

Options
edited 07/01/22

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!

• Employee
Options

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)

=COUNTIFS({RAG}, <> "Blue", {Date}, >= TODAY(31), {Date}, < TODAY(90))

Let me know if this works for you 🙂

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Employee
Options

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)

=COUNTIFS({RAG}, <> "Blue", {Date}, >= TODAY(31), {Date}, < TODAY(90))

Let me know if this works for you 🙂

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

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!

• Employee
Options

Well-done! 🎉 Glad I could help.

October 8 - 10, Seattle, WA | Register now

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!