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.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Count Rows Until A Certain Sum Value Is Reached based off addtl criteria
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[etaf]

SUMtif with a Min

Book1
	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	50	100	250	446
7	48	1-Apr	7					113	50	100	250	442
8	48	2-Apr	10					0	0	0	0	0
9	48	3-Apr	48
10	48	4-Apr	53
11	48	5-Apr	25
12	48	6-Apr	29
13	48	7-Apr	13
14	48	8-Apr	12
15	48	9-Apr	16
16	48	10-Apr	18
17	48	11-Apr	9
18	48	20-Apr	6
19	48	26-Apr	1
20	48	4-May	1
21	48	5-May	14
22	48	6-May	10
23	48	7-May	12
24	48	8-May	14
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
Sheet2

Cell Formulas
Range	Formula
H6:H8	H6	=UNIQUE($A$7:$A$963)
I6:L8	I6	=MIN(SUMIF($A$7:$A$46,$H6,$C$7:$C$46),I$5)
Dynamic array formulas.

 

[chefanator]

Thanks, etaf, but I'm looking to sum the "# of cells" not the values in those cells. For example, for product 48, it took 3 days to reach the threshold of 50, and 4 days to reach threshold of 100, and 15 days to reach threshold of 250. My product list is long, and I want to be able to do this for each unique product #.

 

[Marcelo Branco]

I think this scenario requires a very complicated array formula - I'm using Excel 2010. Maybe someone with Excel 365 can come up with a simpler formula.
Would a macro be acceptable?

M.

 

[Fluff]

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.

 

[Marcelo Branco]

How about

I had created an array formula in Xl 2010 but it was too complicated. As I supposed with Xl 365 the formula is much simpler.

Fluff,
Nice

M

 

[Fluff]

Nice

Thanks
I wouldn't know where to even begin to do that without some of the new functions.

 

[Marcelo Branco]

I wouldn't know where to even begin to do that without some of the new functions.

Just out of curiosity...
Ugly array formula in I6 copied across and down

31052022 Testes.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
7	48	01/04/2022	7					113	2	2	5
8	48	02/04/2022	10					114	1	3
9	48	03/04/2022	48
10	48	04/04/2022	53
11	48	05/04/2022	25
12	48	06/04/2022	29
13	48	07/04/2022	13
14	48	08/04/2022	12
15	48	09/04/2022	16
16	48	10/04/2022	18
17	48	11/04/2022	9
18	48	20/04/2022	6
19	48	26/04/2022	1
20	48	04/05/2022	1
21	48	05/05/2022	14
22	48	06/05/2022	10
23	48	07/05/2022	12
24	48	08/05/2022	14
25	48	09/05/2022	40
26	48	10/05/2022	10
27	48	11/05/2022	14
28	48	12/05/2022	4
29	48	13/05/2022	5
30	48	14/05/2022	6
31	48	15/05/2022	9
32	48	16/05/2022	16
33	48	17/05/2022	11
34	48	18/05/2022	12
35	48	19/05/2022	9
36	48	20/05/2022	7
37	48	21/05/2022	5
38	48	22/05/2022	0
39	113	22/01/2022	13
40	113	23/01/2022	177
41	113	24/01/2022	40
42	113	25/01/2022	11
43	113	06/02/2022	126
44	113	07/02/2022	33
45	113	08/02/2022	15
46	113	09/02/2022	27
47	114	09/02/2022	76
48	114	10/02/2022	10
49	114	11/02/2022	20
Plan1

Cell Formulas
Range	Formula
I6:L8	I6	=IF(SUMIF($A$7:$A$49,$H6,$C$7:$C$49)>I$5,IFERROR(MATCH(I$5,SUBTOTAL(9,OFFSET(INDEX($C$7:$C$49,MATCH($H6,$A$7:$A$49,0)),,,ROW(INDEX($C$7:$C$49,MATCH($H6,$A$7:$A$49,0)):$C$49)-ROW(INDEX($C$7:$C$49,MATCH($H6,$A$7:$A$49,0)))+1)),1)+IF(ISNUMBER(MATCH(I$5,SUBTOTAL(9,OFFSET(INDEX($C$7:$C$49,MATCH($H6,$A$7:$A$49,0)),,,ROW(INDEX($C$7:$C$49,MATCH($H6,$A$7:$A$49,0)):$C$49)-ROW(INDEX($C$7:$C$49,MATCH($H6,$A$7:$A$49,0)))+1)),0)),0,1),1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

M.

 

[Marcelo Branco]

A macro

Sub aTest()
    Dim rData As Range, rThreshold As Range, rProduct As Range
    Dim rD As Range, rT As Range, rP As Range
    Dim lCounter As Long, lTotal As Long, bFind As Boolean
    
    Set rData = Range("A7:C" & Cells(Rows.Count, "A").End(xlUp).Row)
    Set rThreshold = Range("I5:L5")
    Set rProduct = Range("H6:H" & Cells(Rows.Count, "H").End(xlUp).Row)
    
    For Each rT In rThreshold
        For Each rP In rProduct
            lTotal = 0
            lCounter = 0
            bFind = False
            For Each rD In rData.Columns(1).Cells
                If rP = rD Then
                    lCounter = lCounter + 1
                    lTotal = lTotal + rD.Offset(0, 2)
                    If lTotal >= rT Then
                        bFind = True
                        Exit For
                    End If
                End If
            Next rD
            If bFind Then Cells(rP.Row, rT.Column) = lCounter
        Next rP
    Next rT
End Sub

M.