Need to look up costs within a date range and get a percentage of total costs

danielrussell2

New Member
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).

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Replies
0
Views
261
Replies
8
Views
371
Replies
4
Views
478
Replies
3
Views
608
Replies
3
Views
246

1,191,055
Messages
5,984,391
Members
439,883
Latest member
onions44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back