CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulagoogle-sheets-formula
Abhishek
Abhishek

Reputation: 23

Find row when Cumulative sum reaches certain value with condition

I have a table with 3 columns Date, Item and Number. Each row indicates how many items of each Item was received on that date. I am trying to find the date on which cumulative sum reaches 100 or more in that month. Each month will have a target for each item which will be saved in another sheet but for simplicity we can assume that its a fixed number of 100.

Example Data:
Date       Item        Number
1/2/2018   A           10
2/2/2018   B           10
2/2/2018   A           15
5/2/2018   C           25
6/2/2018   A           50
7/2/2018   B           10
7/2/2018   C           10
8/2/2018   A           25
9/2/2018   A           20

I am looking for the formula which should act on the data similar to above and give the result as 8/2/2018 which is the date on which the cumulative sum for Item A reached 100.

Each month will have different target number, and will have different number of entries.

I have tried using SUMIF and adding a additional column etc but this data is just part of a big data and each item limit is saved in a different sheet etc which is not easy to merge. Thanks in advance for help.

Upvotes: 1

Views: 2937

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34230

In Excel only, you can use Offset to develop an array of items and numbers containing 1,2,3...9 rows and then SUMIF to add each of them up. Then use Match to find the first one =100 and Index to find the matching date (in F2):

=INDEX(A2:A10,MATCH(100,SUMIF(OFFSET(B2,0,0,TRANSPOSE(ROW(A2:A10))-ROW(A1)),"A",OFFSET(C2,0,0,TRANSPOSE(ROW(A2:A10))-ROW(A1))),0))

Must be entered as an array formula using CtrlShiftEnter.

EDIT

To find the first sum which is >=100 (in G2):

=INDEX(A2:A10,MATCH(TRUE,SUMIF(OFFSET(B2,0,0,TRANSPOSE(ROW(A2:A10))-ROW(A1)),"A",OFFSET(C2,0,0,TRANSPOSE(ROW(A2:A10))-ROW(A1)))>=100,0))

Sum which reaches exactly 100

enter image description here

Sum which does not reach exactly 100 (the number in row 9 has been changed to 24):

enter image description here

Upvotes: 2

Related Questions