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.
Code:
=DATE(YEAR(TODAY()),(MONTH(TODAY())+1)-(ROW()-ROW(tCalendary[[#Headers],[Date]])-1),0)
, 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
Code:
AND(OR([@Closed]>=DATE(YEAR(ProjDate),1,1),[@Closed]=""),[@Created]<=DATE(YEAR(ProjDate),12,31))
(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!