Count Projects by Year within a range

Chris Mondeau

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!

Paul Newcome

@cmondo You could try something along the lines of...

=COUNTIFS(Start:Start, IFERROR(YEAR(@cell), 0) <= 2020, End:End, IFERROR(YEAR(@cell), 0) >= 2020)

This will also remove the need for your Year columns as it references the date columns directly.

Paul Newcome

@cmondo My solution above uses IFERROR statements to account for blank cells and allows the formula to work.

Stefan

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

In your sheet summary:

=COUNTIF( Year_Start: Year_End; =2020)

Hope this helps

Stefan

Febriandini Bourikas

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

Paul Newcome

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

Paul Newcome

@cmondo You could try something along the lines of...

=COUNTIFS(Start:Start, IFERROR(YEAR(@cell), 0) <= 2020, End:End, IFERROR(YEAR(@cell), 0) >= 2020)

This will also remove the need for your Year columns as it references the date columns directly.

Chris Mondeau

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

Paul Newcome

@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.

Paul Newcome

@cmondo My solution above uses IFERROR statements to account for blank cells and allows the formula to work.

Febriandini Bourikas

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

Stefan

https://community.smartsheet.com/discussion/comment/256089#Comment_256089

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.

Your solution is much more sophisticated and best answer.

Greetings

Stefan