Sum with condition until value reached and return another cell on same line

[Vinch]

Hello,

I have two sheets in my file:
- a table with the amount of sales, per day, for each product (sales per day)
- a second table with each product and the available stock (available stock)

For each product that is availble in stock, I would like to return the amount of days that is took to sell the same quantity that is available in stock.

I already tried a formula as: MATCH(C5,SUBTOTAL(9,OFFSET($C$4,,,ROW($C$7:$C$1000)-ROW($C$4))),1)
where C5 is in my sheet "available stock" and the range "$C$7:$C$1000" is in my sheet "sales per day. → this seems to work but it doesn't take into account my product reference... I cannot find any way to do so

Do you have a better way to calculate this?
I already spent hours on google and other forums but I couldn't find any formula that could work.

Thank you!

 

[duggie33]

I made up a small set of data and found a way to get the estimated days of stock. I think it should get you started...

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Product	Date Sold	Qty Sold		Product	Qty Stock		Product to Evaluate		C
2	A	8/27/2023	14		A	80
3	B	8/4/2023	8		B	75
4	C	8/29/2023	5		C	120		Product	Date Sold	Qty Sold	Running Total
5	A	8/16/2023	3					C	8/29/2023	5	5
6	B	8/11/2023	5					C	8/22/2023	6	11
7	C	8/22/2023	6					C	8/22/2023	7	18
8	A	8/19/2023	8					C	8/22/2023	10	28
9	B	8/28/2023	12					C	8/20/2023	10	38
10	C	8/20/2023	10					C	8/19/2023	14	52
11	A	8/12/2023	5					C	8/8/2023	8	60
12	B	8/10/2023	4					C	8/5/2023	6	66
13	C	8/22/2023	7					C	8/3/2023	11	77
14	A	8/15/2023	5					C	8/3/2023	9	86
15	B	8/12/2023	9
16	C	8/3/2023	11
17	A	8/4/2023	4					Qty Match	# of Days	Days of Stock
18	B	8/11/2023	11					86	27	37
19	C	8/8/2023	8
20	A	8/8/2023	5
21	B	8/28/2023	9
22	C	8/22/2023	10
23	A	8/18/2023	7
24	B	8/3/2023	10
25	C	8/5/2023	6
26	A	8/16/2023	6
27	B	8/15/2023	7
28	C	8/3/2023	9
29	A	8/2/2023	3
30	B	8/2/2023	11
31	C	8/19/2023	14
Sheet4

Cell Formulas
Range	Formula
H5:J14	H5	=LET(filt,FILTER(Table2,Table2[Product]=J1),SORTBY(filt,INDEX(filt,,2),-1))
K5:K14	K5	=SUM($J$5:J5)
H18	H18	=INDEX(K5:K14,MATCH(VLOOKUP(J1,$E$2:$F$4,2,FALSE),K5:K14,1))
I18	I18	=I5-INDEX(I5:I14,MATCH(VLOOKUP(J1,$E$2:$F$4,2,FALSE),K5:K14,1))+1
J18	J18	=ROUNDDOWN(VLOOKUP(J1,$E$2:$F$4,2,FALSE)/H18*I18,0)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J1	List	=$E$2:$E$4

Change the value in J1 to evaluate the other products. This could probably be all smashed together into a single formula with some effort.

Hope it helps,

Doug