How to count projects by status and date range

Febriandini Bourikas

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

Febriandini Bourikas

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?

Paul Newcome

Try something like this...

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

Febriandini Bourikas

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?

Paul Newcome

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))

Febriandini Bourikas

Thank you @Paul Newcome!

Paul Newcome

Happy to help! 👍️