Summarize by hours and status

Mike_M-rpbo 

I came up with a solution, I'll post it here in case anyone in the future has a similar need. I'm sure a more elegant solution could be found. In this case I had to have a solution that works in Excel 2010 so some of newer excel functions were not available.

More generally this problem can be thought of as having a set of objects that are in any one of multiple states throughout the day. The objects can be used for multiple projects but the projects do not overlap in terms of time. The goal is to report on how long each object is in each of the states both daily and over a period of time.

The solution I came up with is two excel worksheets, one to store the data and a second for the report.

The data is structured as I outlined in my initial post. The report is structured as follows:

Cell B1 contains a dropdown list of the projects.

The first column contains the unique dates starting in cell A4. The array formula for this column is:

=IfError(Index(NetHourDetails[Date only],Match(0,CountIf($A$3:A3, NetHourDetails[Date only]),0)),"")

The states are in the third row starting in cell B3. This is also an array formula, it is:

=If(Column()-1>Rows(NetStatus),"", Transpose(NetStatus))

The array formula used for the cells in the report is:

=If(B$3="",0,1)*Sum(If(NetHourDetails[[Net 1]:[Net 15]]=B$3,1,0)*If(NetHourDetails[Date only]=$A4,1,0)*If(NetHourDetails[Protocol]=$B$1,1,0)*NetHourDetails[Hours])

NetStatus is a named range with all the net status values.

Any comments/suggestions are welcome.

Mike