Calculate monthly rental costs

[itsawayoflife]

I would like to track how much I have left to pay for my monthly rent. The problem is that the price of my children is caculated daily and is based on their age. When they are move up an age bracket they cost more per day.

I am having trouble figuring out how to set a formula which will update their price when they move up an age bracket starting (obviously) on their birthday and not affecting the previous month's price. I hope this makes sense.

Thanks for any help you can give!

[Bo_Ry]

I'm not sure about this.

Please key-in Year at H1

I3 Press Ctrl+Shift+Enter and drag down
=SUMPRODUCT(LOOKUP(DATEDIF($N$8:$N$11,TRANSPOSE(ROW(INDIRECT(EDATE(1&H3&$H$1,0)&":"&EOMONTH(1&H3&$H$1,0)))),"y"),{0,1,5,13,18},{2,5.6,7.7,10.55}))

[itsawayoflife]

Wow, it works so good! Thank you so much for that! I'm always impressed by how much you guys know and how you figure out how to do this! Thanks!

I realised that I left one thing out and I can't figure out how to add it to your formula: Each month $200 should be added to that months total + $24 per day (for the parents). So January would be (24*31)+200.

Could you help me with this?

[Bo_Ry]

Please add this and press Ctrl+Shift+Enter

=SUMPRODUCT(LOOKUP(DATEDIF($N$8:$N$11,TRANSPOSE(ROW(INDIRECT(EDATE(1&H3&$H$1,0)&":"&EOMONTH(1&H3&$H$1,0)))),"y"),{0,1,5,13,18},{2,5.6,7.7,10.55,20}))+DAY(EOMONTH(1&H3&$H$1,0))*24+200

I miss 20$ for 18+