0

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.

Improve this question
4
  • 1
    Since you have Dates in 2 columns,, means are start and End Dates for respective expenses?
    – Rajesh Sinha
    Feb 26, 2019 at 5:52
  • 1
    I suspect your 12/11/2019 water end date should be 2018 instead? You're on the right track. Calculate average cost per day for each service for each period. To get calendar months, you need data for periods that cover the month. So you could potentially have some number of days from one period at that rate plus the remaining days from the next period at another rate. Figure out how many days each period contributes and add up the daily rates.
    – fixer1234
    Feb 26, 2019 at 11:55
  • @RajeshS - yes. The two dates represent the first and last day that the bill covers.
    – John Rocha
    Feb 28, 2019 at 5:19
  • @JohnRocha, check the solution I've posted calculates the expenses, read the Caveat,, N.B. part also.
    – Rajesh Sinha
    Feb 28, 2019 at 5:25

2 Answers 2

Reset to default
0

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

Spreadsheet values

These are the formulas on this sheet:

spreadsheet formulas

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)

Improve this answer
1
  • Thank you. I am working through this. I am slow as I do my calcs and checks.
    – John Rocha
    Feb 28, 2019 at 15:09
0

enter image description here

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.

Improve this answer
5
  • Thank you. I am working through this. I am slow as I do my calcs and checks.
    – John Rocha
    Feb 28, 2019 at 15:10
  • Rajesh, The OP wanted to break out the cost into individual months in order to overcome the constraints of the Data set periods. If a bill is $45 and spanned 45 days ($1/day) and 15 of those days are in Dec, the Dec total is $15 dollars plus additional partial amounts. This is done individually for each utility. This answer will display the water cost for Dec as $146.57 (the sum of $77.58 and $68.99). The actual period represented by $146.57 is 10/92018 - 2/14/2019, which includes Dec.
    – Ted D.
    Feb 28, 2019 at 16:37
  • @TedD., thanks for observation,, this is what OP written, I would like to enter the above into a table and then calculate how much Water + Sewer + Trash was for January, February, March, etc.,, and my solution is moving all around it. Also in Caveat (N.B.) Edited section I've show how it's possible to use the Formula for A MONTH ONLY and for ITEM also by alter the DATE range and changing criteria. ☺
    – Rajesh Sinha
    Mar 1, 2019 at 5:46
  • @TedD. Continue,, so it's very much possible by using the same Formula to get Total expense of specific ITEM and MONTH or even for few days by altering the DATE RANGE and ITEM.
    – Rajesh Sinha
    Mar 1, 2019 at 5:53
  • @RajeshS, The OP title is calculate monthly expenses, not add up the bills paid that month. Your comment referenced the OP list of comma separated months. This list is not cumulative, but rather a list of individual months. The OP states each bill span 2 months but with different start/stop points, and not at the beginning/end of the months. This is what the calculation needs to overcome. calculate the number of days covered by the period and then calculate the cost per day then I got stuck ... how to go to the next step of January's total is.
    – Ted D.
    Mar 1, 2019 at 7:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.