Amount of number extracted depending on time period

I have two sheets in a workbook, sheet1 and sheet2 (ATTACHED). Sheet 1 contains information regarding where a worker has lived and for how long (from "In" to "Out" in sheet1).
Sheet 2 contains data regarding the rent for the corresponding apartment. So for "Apartment 1", the worker is supposed to pay for some of January (08-01 2018 - 31-01 2018), for all of February and March,
and for some of April (01-04 2018 -07-04 2018). For every month is a column called "Rent paid", which should be added to the corresponding month column, in a similar fashion as described above.
So I basically need a function/code/macro that looks up the apartments for sheet1 in sheet2 and then returns a value in the "Rent" column for the corresponding apartment, with a calculation as described before.
Ideally, it should distinguish between number of days in a month (31 in Jan, 28 in Feb etc.), but if it is too difficult, assuming 31 days in every month is OK.

In "Rent" for Apartment1 I manually calculated the desired output (the calculation is in the cell).

Thank you very much for your help!

Best regards

Please try at G2

=SUMPRODUCT(LOOKUP(MONTH(ROW(INDIRECT(E2&":"&F2))),ROW($A$1:$A$12),SUBTOTAL(9,OFFSET(Sheet2!$B$1:$C$1,MATCH(A2,Sheet2!$A$2:$A$8,),ROW($A$1:$A$12)*3-3))/DAY(EOMONTH(DATE(2019,ROW($A$1:$A$12),1),0))))

8 to 31 Jan should be =31-8+1 =24 days, not 8 days

Not sure, maybe try this, change in red

Formula:

Please Login or Register  to view this content.