# How to count projects by status and date range

Options

Hi, I'm trying to count the number projects based 2 criteria: status and date range. Both of criteria are located in separate raw sheet I have been getting #unparseable #invalid operation. Below is sample formula used, basically I'm trying to count projects in year 2020 that have "status" complete:

COUNTIFS({Integration Projects: Raw Data Sheet Range 1}, "Complete" , AND ({Integration 2} >=DATE(2020, 01, 01), <=DATE(2020, 12, 31))).

Can anyone help? Thank you

Options

Hi @Paul Newcome , thank you very much, I had to update the reference sheet but it worked!

Now that I'm succeeded calculating number of projects in a certain year, do you know how can I calculate within a certain month of the year?

• ✭✭✭✭✭✭
Options

Try something like this...

=COUNTIFS({Integration Projects: Raw Data Sheet Range 1}, "Complete" , {Integration 2}, IFERROR(YEAR(@cell), 0) = 2020)

Options

Hi @Paul Newcome , thank you very much, I had to update the reference sheet but it worked!

Now that I'm succeeded calculating number of projects in a certain year, do you know how can I calculate within a certain month of the year?

• ✭✭✭✭✭✭
Options

Yes. You would use an AND statement to reference that same range and then use a MONTH function the same way we used the YEAR funciton.

=COUNTIFS({Integration Projects: Raw Data Sheet Range 1}, "Complete" , {Integration 2}, AND(IFERROR(YEAR(@cell), 0) = 2020, IFERROR(YEAR(@cell), 0) = 7))

• Options

Thank you @Paul Newcome!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!