danielrussell2
New Member
- Joined
- Mar 17, 2016
- Messages
- 17
I just want to say first that this may be confusing as I'm new to posting, but I'll do my best to explain.
Ok, first part is this - I have worked up a spreadsheet with invoices (lets call these LI, for Labor Invoices). This spreadsheet is complete - it tells the total cost labor invoices, with the date of the invoice, and allocates costs charge different contractors based on who they should go to. For example, if on 1/1/16 we had $1000 of labor costs, and it decided that "Able" (contractor), "Malibu" (contractor), "Mega" (contractor),and "Tabares" (contractor) all should be charged equally for this work, under their section (each contractor has a set of 3 columns to the right of the LI info) of the spreadsheet, they would each be given a 25% designation, and the total cost for each contractor for that day's L.I. would be $250. An example is below:
Now I am building a new spreadsheet in the same workbook to decide equipment costs (call these EI for Equipment Invoices). I've got columns for the cost, date beginning, and date ending. The date begin and end is the range of time for which I was charged equipment rental costs. Next to these columns, I have a column for each contractor, as shown here:
What I'm hoping to have is a formula that I can insert under each contractor on the EI spreadsheet that will give me a total cost, per contractor, for each EI. This cost would be based off of this:
(per EI, or, per row on EI sheet) Contractor Equipment Cost = Total Equipment Cost x (Total Labor Cost for EI Date Range / Total Labor Cost per Contractor for EI Date Range)
EI G3 EI F3 SUM (LI F3) for EI date range (D3 thru E3) / SUM (LI J3) for EI date Range (D3 through E3)
I hope this makes sense, basically I want to get a total cost for each contractor for each equipment invoice. To achieve this, I would need a percentage that the contractor should owe for the equipment invoice. To get this percentage, I would take the total cost of labor costs for that contractor (within date range of equipment invoice) and divide it by the total labor cost (within date range of equipment invoice), then multiply that number by the total equipment cost.
My issue is getting the totals of labor costs for the date range of the equipment invoice. How would I achieve that? (I probably could've made this a much shorter post by getting to that sooner, but I wanted to give as much info as possible).
Thank you in advance for any of your help!
Ok, first part is this - I have worked up a spreadsheet with invoices (lets call these LI, for Labor Invoices). This spreadsheet is complete - it tells the total cost labor invoices, with the date of the invoice, and allocates costs charge different contractors based on who they should go to. For example, if on 1/1/16 we had $1000 of labor costs, and it decided that "Able" (contractor), "Malibu" (contractor), "Mega" (contractor),and "Tabares" (contractor) all should be charged equally for this work, under their section (each contractor has a set of 3 columns to the right of the LI info) of the spreadsheet, they would each be given a 25% designation, and the total cost for each contractor for that day's L.I. would be $250. An example is below:
Now I am building a new spreadsheet in the same workbook to decide equipment costs (call these EI for Equipment Invoices). I've got columns for the cost, date beginning, and date ending. The date begin and end is the range of time for which I was charged equipment rental costs. Next to these columns, I have a column for each contractor, as shown here:
What I'm hoping to have is a formula that I can insert under each contractor on the EI spreadsheet that will give me a total cost, per contractor, for each EI. This cost would be based off of this:
(per EI, or, per row on EI sheet) Contractor Equipment Cost = Total Equipment Cost x (Total Labor Cost for EI Date Range / Total Labor Cost per Contractor for EI Date Range)
EI G3 EI F3 SUM (LI F3) for EI date range (D3 thru E3) / SUM (LI J3) for EI date Range (D3 through E3)
I hope this makes sense, basically I want to get a total cost for each contractor for each equipment invoice. To achieve this, I would need a percentage that the contractor should owe for the equipment invoice. To get this percentage, I would take the total cost of labor costs for that contractor (within date range of equipment invoice) and divide it by the total labor cost (within date range of equipment invoice), then multiply that number by the total equipment cost.
My issue is getting the totals of labor costs for the date range of the equipment invoice. How would I achieve that? (I probably could've made this a much shorter post by getting to that sooner, but I wanted to give as much info as possible).
Thank you in advance for any of your help!
