I have a table with "from" and "to" dates, plus a daily rate. I would like a formula that calculates the total charges arising by month.
Is there a way to calculate a SUMPRODUCT based on the maximum/minimum of each individual value in the column within the table compared to the start/end date for the month? Just using MAX/MIN within an array of the SUMPRODUCT seems to calculate by reference to the whole column in comparison, which makes sense but isn't what I need.
Attached is an example. On row 3 is the SUMPRODUCT that I need help with. In columns E to I there are individual workings for the two example rows to show the results I am looking for. In the intended implementation of this, however, there won't be space to do it that way. The final version will also be subject to a couple of extra criteria from additional columns in the main table.
If there are any decent solutions to this out there I would be really grateful to know.