Calculating Inventory Sell Through by month based on multiple criteria

I am trying to calculate inventory sell through by month, but am running into a lot of issue. Below is a sample data set. The month will only reach 100% sell through when the quantity sold equals the amount received in a month. In the below sample, you see that 2014-04 achieved 100% sell through because 1 was sold in April, 2 in May, and 7 in June (equaling 10). 2014-05 achieves 87% sell through because there were 5 sold in July, 4 in August, 3 in Sept, and 1 in October (equaling 13). The other issue is that there would be 200,000 rows in the Excel sheet that contain multiple different SKU's, so the formula would have to look at each SKU and calculate only on the group of those similar SKU's. Please advise if I can provide any additional details.

HTML Code: 

How about posting a sample workbook?

Sorry I have to make a pass on this one, at least for now. The task is interesting and well defined so it should spark some interest here on the forum. It may however be more of a VBA task, it is rather advanced.

No need to Jacc has put it before the "Cavalry"

If someone doesn't solve this in the interim - I'll have a look at it tomorrow

Don't know if this helps or not but with a few helper columns and an array formula I got this to work for your data set.

1. Please check with your extended data set though because it was only tested on that set
2. I know the purists on this forum don't like helper columns and some of these could be reduced by incorporating into formulas (I think) but I left it open for you to check easily and frankly it would take a brighter person than me to try and get them all into one formula
3. I had to use an array formula so remember cntl+shift+enter

Good luck

Example(1).xlsx

Try this - in G6 enter:
=IF(E6=0,G5,MEDIAN(0,1,(SUMIF($D$6:$D$19,D6,$F$6:$F$19)-SUMIF(D$5:D5,D6,E$5:E5))/E6))
and fill down.

Can your data be sorted by SKU?

See if this version works better - note the two additional columns A and B - these could be incorporated into the formula if necessary.

Hi AlphaSkidz,

Here's a VBA solution:

Please Login or Register  to view this content.

Here's a quicker version - but the example I saw only went from column C to Column G and from row 4 to row 18??

Please Login or Register  to view this content.