SUMUP Quantity Cumulatively Based on Criteria and Get Date Based On Criteria

[Rajeshkumar R]

Dear Friends,

Attached Excel sheet Contains Two Worksheet as elaborated below: -

Material Request History: -

Here we record Item wise what is the quantity is been requested on daily basis.

Note: For single day there will be multiple requests for same item with different Quantities

Material Issuance History: -

Item wise date wise issued qty captured here

Note: For single day there will be multiple issuance for same item with different Quantities

Support Required: -

For any Item, From the Date of Request, The Formula should calculate issued qty from another sheet, Once Cumulatively added up Qty reaching to >=0, That date should be updated against “Request Met Date” Column

Note:
1. Basically, this is to track the time taken from date of request to issuance
2. Though attached sheet contains only some 5 different items, my live file contains more than 3000+ Items

Looking forward your suggestion to meet subjected Requirement!

Thanks & Regards,
Rajeshkumar R

[Bo_Ry]

Please try at Request History
D2

Please Login or Register  to view this content.

confirm by Ctrl+Shift+Enter
array formula will be slow with more data.

or with helper column
E2
=IFERROR(INDEX('Material Issuance History'!$B$2:$B$49,MATCH(TRUE,INDEX(SUMIFS(C$2:C2,A$2:A2,A2)<='Material Issuance History'!$D$2:$D$49*('Material Issuance History'!$A$2:$A$49=A2),),)),"")

and Issuance History D2
=SUMIFS(C$2:C2,A$2:A2,A2)

[Rajeshkumar R]

Dear Bo_Ry,

Thanks a lot for your immediate suggestion; Both your suggested formula's are working amazingly!

However, since I have more number of items, I choose to go with helper Column Formula!

Thanks & Regards,
Rajeshkumar R