Combining Look-up, If and Sumifs

[geng]

Attached is a sample workbook that I'm trying to work with fromulas.

This is actually the same probelm I have in:
http://www.excelforum.com/excel-work...nd-sumifs.html

but this time, it's more complicated because of the details.

with Column C, what I want to return is:
If Column B >= 1, sum data on Data tab column B with the same code(vlookup(File!A7, Data!A2:A159,1,false)), until it reaches the value of column B.

I tried formulating but I can't get the correct formula, please see my formula in I7.

With columns E and G, I can't even think of a clearer description, but please see my notes on the side.

been working on this since May and I hope I can do this perfectly this time.

Thanks from all the help I get in this forum!

And thank you in advance for helping me with this project!

[DonkeyOte]

If I've understood....

First as I see it the result you refer to for 1004 should be 13000 rather than 10600 - ie is missing 2 rows of data (19 & 20)

The complexity is obviously populating Dates of Production on File sheet.

If you wish to avoid VBA I suggest you adopt a similar process to before...

NOTE: below is working off assumption that (per the sample) Data sheet is sorted by CODE in Asc order.

Please Login or Register  to view this content.

With the above in place.... assuming XL2007

Please Login or Register  to view this content.

If "commitment" column on Data sheet is only populated where Cleared = N then you can change Col G to work along similar lines to that used in E, ie:

Please Login or Register  to view this content.

This is quicker than the earlier version given it uses binary search based match of code and nothing else (ie. no boolean test etc...) and thus processes a much smaller subset of data.

[geng]

Thank you DonkeyOte! This is a big help!
But actually, for 1004 it should be 10,600 instead of 13000
This is because tha "shortfall"(Column B) is only 9,640, so I only needed a quantity that can satisfy it.
And the value that can satisfy it is 10,600(should be in column C)
And in column E, it should list:
1500 - 20101003
3000 - 20101024
2500 - 20101030
1200 - 20101107
1200 - 20101128
1200 - 20110114

so the last two rows should not be included in the list:
1200 - 20110128
1200 - 20110102

and in column G should be:
abcdef

since the last value in the list is
1200 - 20110114

Thank you so much again DonkeyOte! I appreciate your help!

[DonkeyOte]

OK, if we assume that:

a) a given Code appears only once per File list

b) you wish to conduct only one set of calculations per Code

then continuing on from the prior suggestions I would suggest first adding:

Please Login or Register  to view this content.

and then with the above in place to amend the formulae suggested previously such that:

Please Login or Register  to view this content.