Originally Posted by ArviLaanemets

OK. Here in attachment I added a report which is closest I was able to create with different statuses on different steps. I'm skeptic how good it is to use, but the decision is yours!

Your original table didn't qualify, as all those empty rows made calculations too cumbersome.

Edit: The design allows to enter projects from more than 1 year. The posted version marks latest status of any step of project as current. At first I started to design the workbook so, that you can select a date, for which current status is calculated, even added a sheet Calendary (now hidden) and created a Named Range lDates to select dates from there, but realized it would take too much time, and dropped the idea. But you can continue with it:
1. Currently the calendary sheet displays dates from 01.01.2017 to today. Too much dates IMHO. Design it to display last dates of month instead, p.e.
, which displays last days of month from current month down. And the table tCalendary can be made for fixed number of months, or until certain month of certain year;
2. On sheet Projects, instead of todays date displayed, the field B2 would have data validation list with =lDates as source. When a date for ProjDate is selected, tProjectSteps[IsCurrent] is calculated according to selected date;
3. On Projects sheet, add a field Closed;


4. In all formulas for status on sheet Projects, instead SUMIFS(from tProjectSteps) use (SUMIFS(from tProjectSteps)*AND(from tProjects)), where AND(from tProjects) is like
(It is not tested, the idea is, that displayed is status only, when project is active in year, where the date ProjDate belongs).

NB! The attached file is old!

Originally Posted by IBRA2018

2- About the year 2017. This is an example only and the sheet can be named anything, but I create new sheet for every year

Originally Posted by ArviLaanemets

And create a report sheet for new year too (and maybe some other too, as you find that you need some additional reports)! And then after a year again new sheets etc.

Really you need a sheet ProjectSteps to enter most of info (sheet Projects is needed for data entry only when you register a new project - and when you close an ended one). Instead of designing a new report every year (Btw, the sheet Projects serves really also as a report), you design reports where you select year/date and maybe some other conditions, and a report for those conditions is displayed (a single sheet for 1 kind of report). And when the number of registered project steps grows too much and as result the workbook will slow down (it depends on your computer, but unless you have really crappy one some 20000 rows must work OK), you archive the workbook with different name, clear old data (or too old data), and continue with same workbook. You need to redesign the workbook only, when you want to add some new functionality to it.

An additional bonus for such design is, that it allows to handle projects, which take several years of work.

Originally Posted by IBRA2018

What I did I added more feeds into the Sheet 2017, starting from Project No. 2017010009 but the results doesn't reflect as it should be for the rest which is under 2017010009 .

Originally Posted by ArviLaanemets

1. The formulas in 4 columns on sheet 2017 (H1...H4) meant to be hidden must have an additional check. P.e.

Code:

IF(OR(ROW()=EVEN(ROW());$G6<>"In Prog");0;COUNTIF($G$5:$G6;"In Prog"))

2. The names CntCompleted, CntInProg, CntNA and CntPending I created based on your example are non-dynamic currently. Change the last row number in formula from 21 to 29 (or redesign these names as dynamic ones.

3. In Report sheet, all formulas referred to ranges like '2017'!$A$6:$A$21 or '2017'!$L$6:$L$21. Change the last row number from 21 to 29 (or use dynamic ranges instead).