Sum Column Until Condition is reached

[rajdeepkhurana]

Hi People,
This is a great community and I have seen some really good answers to questions.. so thanks in advance.

I have the following workbook -:

Sheet1:
Date : ItemName : Qty
01/01/2012 ItemA 7
02/01/2012 ItemA 6
02/01/2012 ItemB 8
02/01/2012 ItemA 7
03/01/2012 ItemA 7


Sheet2:
Cell A1 = 20
Cell A2 = ItemA
Cell A3 = 01/01/2012
Cell A4 = 03/01/2012

I want to sum the quantity column in Sheet 1 until the total reaches the goal value of 20 (cell reference A1 IN SHEET2) and then return the row number where it reaches or crosses 20. The sum should be applied only for ItemA (cell reference A2 in sheet2) and only between the dates of 01/01/2012 and 03/01/2012 (cell reference A3 and AR in Sheet2), all records for other dates and other items should be excluded.

So, In the above example, formula should evaluate to "5", which is the 5th row.

I came very close to getting the solution I need in this post -:
http://chandoo.org/forums/topic/sum-...column-counted

Only issue is, I cant really filter records in the above solution.

I reallly hope someone can help and thanks a ton.

Rgds

[Richard Buttrey]

Hi,

Perhaps the easiest way which avoids a complicated MMULT() function is a helper column D. So in D2 copied down

Please Login or Register  to view this content.

I question your suggested answer. You asked for the row number where the goal is either reached or exceeded. In your example data the figure of 20 is reached (but not exceeded) on row 4. However assuming row 4 is the correct answer then the function is

Please Login or Register  to view this content.

[rajdeepkhurana]

Hi Richard,
Thanks for your response. I will try out the formulas and write back soon.

My suggested answer is row "5" because -:
1.) I am treating data headers as row 1. "Date : Item Name : Quantity"
2.) Row number 4 has ItemB, and we are excluding this from our totals. Which brings us to row number 5.

[dredwolf]

Aaah...okay, normally you would exclude headers so the formula would return data in a manner acceptable to the Offset/Index functions as well (similar to the Match() function), but if you want the 5 instead use Richards formula and add "+1" to the end of it

As to my exploring the solution in the link you provided, still no real success there, although some "interesting" (LOL) results
hope that helps