Hello @Stefan_Stoyanov ,
I think your “Correct answer should be” is not correct. Just looking at Person 1, checking in on Jan 1, the rate would be 50 euro. Up to Jan 9, the rate would stay the same, so that is 450 euro.
On Jan 10, the rate becomes 100 euro.
Etc.
So the totals are a bit different from what you think.
The formula that works is like below. It’s not a beauty, but what you are asking is not very standard.
I requested edit access to your doc and I will add a column with the correct totals (I think) upon granted access.
edit (2) - corrected formula (added one more max() to catch exceptional situations)
