CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
caaax
caaax

Reputation: 460

Spreading cost per month over duration of months in Sheets

I have a tricky problem in Sheets.

I need a formula to calculate running total costs for each month from Nov 2019 on wards (column B).

Currently, my formula for B2 is:

=SUMIFS($F$2:$F$6,$E$2:$E$6,">="&A2,$E$2:$E$6,"<="&(EDATE(A2,1)-1))

Basically, this finds all values in cells F2:F6 whose dates (in column E) match that of A2.

E.g. Cell B3 is the total cost for December, so cells F3 and F6 are a match (200 + 300 = 500)

However, this does not take into account the duration of the cost (column G).

This means that the total cost for December 2019 (cell B3) should actually be 600 (because the November cost duration lasts 12 months). Meaning there is a cumulative cost for the duration of months the cost lasts for.

sheets

I am pretty much stuck on this. If anyone could help that would be great!

Upvotes: 1

Views: 476

Answers (1)

user 88 91
user 88 91

Reputation: 65

So as long as the date given in column A intersect between "start date" and "start date" + x months the cost per month should be applied?

So according to this, Jan-2020 will have a cost of 600 in this example: enter image description here
Sant/Falskt = True/False

Green rows = date intersect between Start and End date

If that is correct then this formula should do the job, paste in B2:
=SUMPRODUCT((--(A2>=DATE(YEAR($E$2:$E$6),MONTH($E$2:$E$6),1))*--(A2<DATE(YEAR($E$2:$E$6),MONTH($E$2:$E$6)+$G$2:$G$6,DAY($E$2:$E$6))))*$F$2:$F$6)

Upvotes: 0

Related Questions