Hello guys!

I am currently writing my Masters thesis and need to organize data for the empirical research, which I've mostly done apart from this particular type of data.

There is a list of power plant outages that took place during the year, indicating the start time and duration of the outages of various power plants, as well as the amount of electricity not available due to each of the outages. See file attached. Example 1.xlsx

I need a formula that will take the value from column C (unavailable capacity) on the sheet "List of outages" and transfer it into the column C of the sheet "Outages sorted by hours" in the number of cells listed in the column E "Duration" (sheet "List of outages") starting with the hour marked in D "Outage begin" (sheet "List of outages"). Note that for every unique hour in the column A sheet "Outages sorted by hours" there might be more than one value so that they are to be summed up in the resulting column.

I wrote the formula, which can be seen in the column "C" sheet "Outages sorted by hours", but it does not work quite right, as was originally written for a list of failures with a need to shift begin hours, for which the elements highlighted bold are responsible. =SUMPRODUCT ((A4>=$F$4:$F$36+"1:0")*(A4<$F$4:$F$36+($G$4:$G$36+1&":0"))*SUBSTITUTE($H$4:$H$36;".";"";)). Manually calculated sums of values ​​are in column G (sheet "Outages sorted by hours"), and differences between values calculated with a formula and manually calculated (and correct) results are in column H. I now edited the list of outage begins and there is no need to shift the first hour as it was initially, but the simple removal of bold elements in the formula above does not help to come to a correct result.

I want to ask you guys to help me correct the formula or find other solution to the problem! I would be very grateful!

Best regards
Michael