This is my first inquiry! Here’s a link to my introduction http://www.excelforum.com/hello-intr...49#post3318749

I have attached an example spreadsheet to illustrate the situation I have described below.
Example Inv Listing & Purch History.xlsx

What I’m working with:
I have an inventory listing with inventory quantity on hand as of 12/31/2012 (see “Inv Listing” tab, column B). I also have listing of purchases made for each of the items on that inventory listing (see “Purch Hist” tab).

What I Need Help With:
I have been trying to write a formula in column C on the “Inv Listing” tab that will calculate the value of the quantity on hand for each item based on the FIFO (First in, First out) accounting method.

For example, item number 1011 on the “Inv Listing” has 15 items on hand. I want the formula to start with the most recent purchase of 8 units @ $6.00/unit on 12/31/2012 (row 2 on “Purch Hist” tab), then add 3 units @ $5.75 (row 3 on “Purch Hist” tab), and continue to work backwards until it gets to the purchase of 12 units @ $5.00/unit on 10/02/2012 (row 4 on “Purch Hist” tab). Once the formula gets to that purchase, I want it to only take 4 of the 12 items purchased at $5.00. The formula should calculate a value of $85.25 for the 15 units of item 1011 on hand at 12/31/2012 on the “Inv Listing” tab row 7.

My initial thought was to use a =sumifs() formula to accomplish this, but I quickly realized I have no idea how to incorporate the quantity on hand to tell the formula to stop.

Other Notes:
I have manually calculated what the result should be in column D on the “Inv Listing” tab and hard coded the numbers. Feel free to manipulate the formula in column C or add a column in any response.

Please note that while this spreadsheet only has 5 inventory items and the purchase history is short and nicely grouped, the actual data will have thousands of items and there will be several thousand purchases.

I'm working in Excel 2007.

I have searched several different forums across the web for a similar situation, but have found nothing. I have chosen to use excelforum.com to post this because all responses seem to be very clear, concise, timely, collaborative, and awesome all around. Thanks in advance for any help you can provide!