# Count Projects by Year within a range

✭✭✭✭✭✭

Hi All!

I've been scratching my head with a way to count all projects where the Start Sate or End Date have a year that matches a bucket.

Fore example, if a project's start date is 2020 or end date is 2020, then I need to count it in the 2020 sheet summary. A project where the start is 2019 and end is 2020 would also be added, and the same case for a project with the start of 2020 and end of 2021.

I've been trying to do this with a combination of countifs and ors, but coming up blank. Any suggestions? Thanks!

• ✭✭✭✭✭✭

Hi,

just a quick idea because no time left to try today.

If you do not need the columns "Year_Start" and "Year_End" to show the years, the you could use something like this:

In the Year_End:

=IF(YEAR(Finish1) = YEAR(Start1); ""; YEAR(Finish1))

=COUNTIF( Year_Start: Year_End; =2020)

Hope this helps

Stefan

Smartsheet Consulting, Solution Building, Training and Support.

Projects for Processes and for People.

• Hi Stefan, 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 as results. Below is sample formula used. Can anyone help? Thank you COUNTIFS({Integration Projects: Raw Data Sheet Range 1}, Complete , AND ({Integration 2} >=DATE(2020, 01, 01), <=DATE(2020, 12, 31))).

• ✭✭✭✭✭✭

@Febriandini Bourikas I have replied in your other post with a possible solution.

• ✭✭✭✭✭✭
edited 07/09/20

@Paul Newcome thank you sir! That's exactly what was missing!

• ✭✭✭✭✭✭

@Stefan I don't think your solution will work. If the Start and End years are both 2020, then the End Year column will be blank and thus missed in the 2020 count.

• Thank you very much @Paul Newcome , I really appreciate it! I actually just commented your answer, asking a new question about how can we calculate projects within a specific month within particular year

• ✭✭✭✭✭✭

Hi @Paul Newcome , My formula in the summary checks both columns at once and would double count 2020 in the case you describe. That's why I remove the 2020 in the End_Year column. Just a quick shot.

Greetings

Stefan

Smartsheet Consulting, Solution Building, Training and Support.

Projects for Processes and for People.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!