Finding Cost Per Month with Multiple Date Ranges and Variable Costs

[gooutside]

Good afternoon, I am attempting to build a table to find monthly costs of utility bills that arrive mid-month. From what I have been able to google on this, I will need to be using a combination of a MIN()/MAX() counter and a SUMPRODUCT() solution.


Perhaps some examples will make this most clear. There are two tables we're working on, here:

Table 1 - Bills

	A	B	C	D	E
1	Billing Start Date	Billing End Date	Bill Amount	Days Between	Daily Amount
2	11/10/16	11/14/16	$20.75	4d	$5.19
3	11/15/16	12/14/16	$109.88	29d	$3.79
4	12/15/16	1/13/17	$151.71	29d	$5.23
5	1/14/17	2/14/17	$146.31	31d	$4.72
6	2/15/17	3/16/17	$136.54	29d	$4.71

There will be a separate table that will list out each month vertically (this is where I run into issues with google searches - everything I see if creating a long horizontal grid for each row in the table above. I do not want this.)

Here is an example of the table with the hoped-for formulas:

Table 2 - Summary

	A	B
1	11/1/2016	$77.61
2	12/1/2016	$136.74
3	1/1/2017	$148.23
4	2/1/2017	$127.31
5	3/1/2017	$75.36

For clarity, the value in Table 2's B1 should examine the full array in Table 1 and do math that would multiply the number of days in each row of Table 1 with the cost per day for each row. In this example, doing the math by hand, it would be (4 days * $5.19/day) + (15 days * $3.79/day).

As time goes by, Table 1 will grow by row, vertically, as will Table 2. New dates and bills will be added to the bottom of the tables.

Through my searching, I have been trying to adapt this formula into something that can be used across a range of dates, rather than just row-by-row:

Please Login or Register  to view this content.

This is effective at counting the number of days per row, but not for a range of rows with multiple dates. I assume I must incorporate this MAX/MIN calculation into some kind of SUMPRODUCT() calculation, but I'm struggling a lot with how to do this.

Lastly, just to make things super complicated: using array formulas can not be considered a solution for this. I can't use array formulas in this spreadsheet and I can not change this.

Any help is very kindly appreciated.

[6StringJazzer]

I don't know how to do this without an array formula, even we use SUMPRODUCT. Someone else may know. I respect your restriction, but I'm really curious about why this would not be allowed. I have heard of companies prohibiting VBA and I understand why but I cannot think of any reason to prohibit array formulas. They are a powerful and essential feature of Excel.

This requires calculating the monthly amount row-by-row. I have attached a file showing a solution to this that uses a "helper" table.