Count Rows Until A Certain Sum Value Is Reached based on addtl criteria

[chefanator]

Hi - I am trying to sum the # of cells across a period of time until a certain threshold is reached from the value of those cells, but for multiple product codes in the same column. I was able to create the formula for sum until the threshold, but I am having trouble defining the second criteria for only focusing on the total units sold for a specific product. Can anyone assist. The formula I am using thus far is:

=MATCH(I$6,SUBTOTAL(9,OFFSET($C$7,,,ROW($C$7:$C$1000)-ROW($C$7))),1)

However, I need to add in an additional criteria to only focus on counting values in column C that match the product code in column H with the reference column A.

For example, in the attached screenshot, I want to find the "days to reach 50" for product 113 only.

Thanks in advance!

sales analysis.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	OMIT	(blank)
3	NEW	NEW
4									threshold
5	Sum of ordered_item_quantity							product #	50	100	250	500
6	product #	day	Total					48	3
7	48	1-Apr	7					113	3
8	48	2-Apr	10					114	3
9	48	3-Apr	48					115
10	48	4-Apr	53					116
11	48	5-Apr	25					117
12	48	6-Apr	29					135
13	48	7-Apr	13					137
14	48	8-Apr	12					138
15	48	9-Apr	16					141
16	48	10-Apr	18					142
17	48	11-Apr	9					143
18	48	20-Apr	6					145
19	48	26-Apr	1					147
20	48	4-May	1					149
21	48	5-May	14					150
22	48	6-May	10					151
23	48	7-May	12					156
24	48	8-May	14					157
25	48	9-May	40
26	48	10-May	10
27	48	11-May	14
28	48	12-May	4
29	48	13-May	5
30	48	14-May	6
31	48	15-May	9
32	48	16-May	16
33	48	17-May	11
34	48	18-May	12
35	48	19-May	9
36	48	20-May	7
37	48	21-May	5
38	48	22-May	0
39	113	22-Jan	13
40	113	23-Jan	177
41	113	24-Jan	40
42	113	25-Jan	11
43	113	6-Feb	126
44	113	7-Feb	33
45	113	8-Feb	15
46	113	9-Feb	27
sales analysis

Cell Formulas
Range	Formula
H6:H24	H6	=UNIQUE($A$7:$A$963)
I6:I8	I6	=MATCH(I$5,SUBTOTAL(9,OFFSET($C$7,,,ROW($C$7:$C$1000)-ROW($C$7))),1)
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

 

[chefanator]

Thank you Fluff, this is exactly what I needed. I was reading about a dynamic array but wasn’t quite sure how to write it. thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[chefanator]

How about

++Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	OMIT	(blank)
3	NEW	NEW
4									threshold
5	Sum of ordered_item_quantity							product #	50	100	250	500
6	product #	day	Total					48	3	4	15	#N/A
7	48	01-Apr	7					113	2	2	5	#N/A
8	48	02-Apr	10					0
9	48	03-Apr	48
10	48	04-Apr	53
11	48	05-Apr	25
12	48	06-Apr	29
13	48	07-Apr	13
14	48	08-Apr	12
15	48	09-Apr	16
16	48	10-Apr	18
17	48	11-Apr	9
18	48	20-Apr	6
19	48	26-Apr	1
20	48	04-May	1
21	48	05-May	14
22	48	06-May	10
23	48	07-May	12
24	48	08-May	14
25	48	09-May	40
26	48	10-May	10
27	48	11-May	14
28	48	12-May	4
29	48	13-May	5
30	48	14-May	6
31	48	15-May	9
32	48	16-May	16
33	48	17-May	11
34	48	18-May	12
35	48	19-May	9
36	48	20-May	7
37	48	21-May	5
38	48	22-May	0
39	113	22-Jan	13
40	113	23-Jan	177
41	113	24-Jan	40
42	113	25-Jan	11
43	113	06-Feb	126
44	113	07-Feb	33
45	113	08-Feb	15
46	113	09-Feb	27
Data

Cell Formulas
Range	Formula
H6:H8	H6	=UNIQUE($A$7:$A$963)
I6:L7	I6	=LET(f,FILTER($C$7:$C$963,$A$7:$A$963=$H6),r,ROWS(f),XMATCH(I$5,MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),f),1))
Dynamic array formulas.

Thank you for this! What if I wanted to sum the values in column C for the first 7 / 14 / 21 days in which values are present for?

 

[Fluff]

As this is a different question, it needs a new thread. Thanks