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:
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.
Bookmarks