Hello all,
I am working with a table containing a list ofprojects, project start dates, project end dates and project costs per day. Theprojects can last any number of days and end in different months than theystart (eg Project 2). This table is shown below in range A1:D5:
I want to calculate the total expenditure in each month inthe right hand table in column H, but I can’t figure out a formula that willcalculate the amount each project costs in a given month and sums these values.My attempts to date included finding the number of days overlap between theprojects start and end dates and the month start and end dates, and multiplyingthis number of days by the daily cost of each project. For example, for Project0 and the month of December 2018, I would use the following formula:
=(MIN(G2,C2)-MAX(F2,B2)+1)*(D2)
The problem here is this only produces a costper month for an individual project, and I need a formula that will check theoverlap for all the projects in the table against each months start and enddate, and then sum the product of the number of days overlap and the cost perday. I’m guessing an array formula must be used to do this, but I aminexperienced with them? Really appreciate all help with this, many thanks.
I am working with a table containing a list ofprojects, project start dates, project end dates and project costs per day. Theprojects can last any number of days and end in different months than theystart (eg Project 2). This table is shown below in range A1:D5:
I want to calculate the total expenditure in each month inthe right hand table in column H, but I can’t figure out a formula that willcalculate the amount each project costs in a given month and sums these values.My attempts to date included finding the number of days overlap between theprojects start and end dates and the month start and end dates, and multiplyingthis number of days by the daily cost of each project. For example, for Project0 and the month of December 2018, I would use the following formula:
=(MIN(G2,C2)-MAX(F2,B2)+1)*(D2)
The problem here is this only produces a costper month for an individual project, and I need a formula that will check theoverlap for all the projects in the table against each months start and enddate, and then sum the product of the number of days overlap and the cost perday. I’m guessing an array formula must be used to do this, but I aminexperienced with them? Really appreciate all help with this, many thanks.
Last edited by a moderator:
