Jtdylktuy

I don't even know the correct way to phrase this title. Thank you in advance for your patience.

I have multiple bills that span different time frames, and I need come up with monthly amounts.

For example:

10/09/2018 - 12/11/2019    Water    $ 77.58

11/15/2018 - 01/14/2019    Sewer    $124.12

12/01/2018 - 01/31/2019    Trash    $ 43.14

12/11/2018 - 02/14/2019    Water    $ 68.99

02/01/2019 - 03/31/2019    Trash    $ 43.14

They each span 2 months but with different start/stop points, and not at the beginning/end of the months.

I would like to enter the above into a table and then calculate how much Water + Sewer + Trash was for January, February, March, etc.

My first thought was to calculate the number of days covered by the period and then calculate the cost per day. But then I got stuck because I don't know how to go to the next step of January's total is....

I'm not sure of the correct way to approach this problem. I would appreciate any pointer tips, web page references, what do I actually call/categorize this problem as -- I don't even know the words.

Thank you.

You are on the right track. Here is a mockup using helper cells and columns to simplify things a bit:

These are the formulas on this sheet:

Here are the formulas as text to copy and paste.

Beginning and Ending of Month:
=DATEVALUE( G1 & " 1, " & F1)
=EOMONTH(F2,0)

Per Day Cost: =D3/DAYS(B3,A3)

Is beginning of month in billed date range? Number of days from beginning of this month to the end of the billed date range or the end of month, whichever comes first. Multiplied by the cost per day.

=IF(AND(F$2>=$A3,F$2<=$B3),$E3*DAYS(IF($B3<G$2,$B3,G$2),F$2) +1,"")

End of month in range ... with additional restraint that the beginning of month is not also in this date range. If it is, then the billed range covers the entire month and has already been computed in the beginning of month column.

=IF(AND(G$2>=$A3,G$2<=$B3,$A3>F$2),$E3*DAYS(G$2,IF($A3>F$2,$A3,F$2)) +1,"")

Final Totals, look in the month beginning and month ending columns. If there is a number, it is part of the cost for this month. Sum both columns for every entry corresponding to this monthly utility (row) heading:

Water =SUMIF(C3:C7,H2,F3:G7)

Sewer =SUMIF(C4:C8,H3,F4:G8)

Trash =SUMIF(C5:C9,H4,F5:G9)

Using your Data Set, I would like to suggest this Formula to get the Total expenses of Water, Sewer & Trash in January to March.

=SUM(SUMIFS($D$2:$D$6,$C$2:$C$6,{"Water","Sewer","Trash"},$A$2:$A$6,">=01/01/18",$B$2:$B$64,"<=03/31/19"))

To make the Formula Dynamic I would like to suggest use, Cell references for Start and End Dates as Criteria.

And your Formula should be:

=SUM(SUMIFS($D$2:$D$6,$C$2:$C$6,{"Water","Sewer","Trash"},$A$2:$A$6,">="&$E$1,$B$2:$B$6,"<="&$F$1))

N.B.

• Since your Data set has Dates between 2018 and 2019 so that I've used that period in 1st Formula. Otherwise it may be JAN'19 to MAR'19.




• E1 and F1 are cell contains Start and End Dates.

Edited:

By doing little modifications in E1 and F1 for Start & End Date the same formula can be used to get other values also.

• For example, to get Total expenses of (Water+Sewer+Trash) for January only, write 01/01/19 in E1 and 01/31/19 in F1.




• Another should, Total expenses of WATER for 3 months, for this just eliminate Sewer & Trash from the list and keep {"Water"} only, or you may write like this also, $C$2:$C$6,"=Water".




• Adjust Cell references in the Formula as needed.