# Minimum no. of consecutive cells such that the sum is greater than a target number

#### ABHISKV4

##### New Member
Query: Need to find minimum no. of consecutive cells, from above yellow highlighted range, such that the sum is >= "sum to Look for" (here 813). In case of a tie, i.e. say there are two sets of 8 cells which give value >= "sum to look for", then the block whose sum is higher, should be shown as the result.

EXCEL - Minimum range of cells such that the sum is greater than a target number 06Sep2020 FINAL.xlsx
BCDEFGHIJKLMNOPQRS
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
3Sales Qty -->15595109159688673119102744888941951601625813
Sheet1 (2)
Cell Formulas
RangeFormula
R3R3=SUM(C3:Q3)
S3S3=ROUNDUP(50%*R3,0)

Further illustration can be seen below:
EXCEL - Minimum range of cells such that the sum is greater than a target number 06Sep2020 FINAL.xlsx
ABCDEFGHIJKLMNOPQRST
7
81559510915968867311910274488894195160Finding Sum >= 813No. of cells such that >=813Remarks
91551552503595185866727458649661040108811761270146516258648Tie for 8 cell range, but not final answer as 864 is <= 880
10959520436343151759070981188593310211115131014708859
111091092683364224956147167908389261020121513758389
12159159227313386505607681729817911110612668179
136868154227346448522570658752947110794710
14868615927838045450259068487910398799
1573731922943684165045987939539539
161191192212953434315257208808808Tie for 8 cell range, this is final answer as 880 is >= 864
1710210217622431240660176100
18747412221030449965900
19484813623042558500
20888818237753700
21949428944900
2219519535500
23160
24
Sheet1 (2)
Cell Formulas
RangeFormula
C9C9=C8
P9:Q21,Q22,O9:O20,N9:N19,M9:M18,L9:L17,K9:K16,J9:J15,I9:I14,H9:H13,G9:G12,F9:F11,E9:E10,D9P9=O9+P\$8
R9:R22R9=MIN(IF(D9:Q9>=\$S\$3,D9:Q9,""))
S9:S22S9=IF(R9=0,0,COUNTA(\$C9:INDEX(C9:Q9,0,MATCH(R9,C9:Q9,0))))
P22P22=P8
D10D10=D8
E11E11=E8
F12F12=F8
G13G13=G8
H14H14=H8
I15I15=I8
J16J16=J8
K17K17=K8
L18L18=L8
M19M19=M8
N20N20=N8
O21O21=O8
Press CTRL+SHIFT+ENTER to enter array formulas.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### jasonb75

##### Well-known Member
Perhaps another member can improve this, the formula works as required but is not very efficient.

Note:- Iterative calculation must be enabled for this to work. (Excel > File > Options > Formulas > check box for 'Enable iterative calculation' > Ok). If not done you will get a circular reference warning and the result will always be 0.
Book1
BCDEFGHIJKLMNOPQRST
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
3Sales Qty -->15595109159688673119102744888941951601625813880
Sheet1
Cell Formulas
RangeFormula
R3R3=SUM(C3:Q3)
S3S3=ROUNDUP(50%*R3,0)
T3T3=MAX(INDEX(VALUE(TEXT(SUBTOTAL(9,OFFSET(\$C3,0,COLUMN(\$C3:\$Q3)-COLUMN(\$C3),1,TRANSPOSE(COLUMN(\$C3:\$Q3))-COLUMN(\$C3)+1)),"[>"&\$R3&"]\0;\0;0;")),0,AGGREGATE(15,6,(TRANSPOSE(COLUMN(\$C3:\$Q3))-COLUMN(\$C3)+1)/(VALUE(TEXT(SUBTOTAL(9,OFFSET(\$C3,0,COLUMN(\$C3:\$Q3)-COLUMN(\$C3),1,TRANSPOSE(COLUMN(\$C3:\$Q3))-COLUMN(\$C3)+1)),"[>"&\$R3&"]\0;\0;0;"))>=\$S3),1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### ABHISKV4

##### New Member
@jasonb75 , formula works absolutely the way i wanted, many many thanks for your time and help on this. As rightly mentioned by you, now I look forward to more efficient formulas to achieve the same result, thanking everyone in advance for your contribution on this.

#### jasonb75

##### Well-known Member
I'm not sure that a more efficient version will be possible without a matrix table like the second sheet in post 1. This was the only way that I could find to produce the matrix as part of a single formula.

One thing that I should mention is that it will not work properly if you have any negative values in the row from columns C to AE inclusive. Although not obvious from the formula, the range used crosses over the column with the formula and continues into a number of columns to the right, empty cells, positive numbers or text will not affect the formula but negative numbers or errors will.

#### Peter_SSs

##### MrExcel MVP, Moderator

Assuming no numerical values in the row to the left of the list (ie A3:B3), try. Not widely tested.

Edit: This is similar to Jason's approach except ..
- Using Excel 365's SEQUENCE function
- Overlapping the range to the left (including off the sheet) to avoid going over this or the other formula cells

20 09 06.xlsm
ABCDEFGHIJKLMNOPQRST
1
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
3Sales Qty -->15595109159688673119102744888941951601625813880
Columns to sum
Cell Formulas
RangeFormula
R3R3=SUM(C3:Q3)
S3S3=ROUNDUP(50%*R3,0)
T3T3=AGGREGATE(14,6,INDEX(SUBTOTAL(9,OFFSET(Q3,,-SEQUENCE(,COLUMNS(C3:Q3),0),,-SEQUENCE(COLUMNS(C3:Q3)))),AGGREGATE(15,6,SEQUENCE(COLUMNS(C3:Q3))/(SUBTOTAL(9,OFFSET(Q3,,-SEQUENCE(,COLUMNS(C3:Q3),0),,-SEQUENCE(COLUMNS(C3:Q3))))>=S3),1),0),1)

Last edited:

#### ABHISKV4

##### New Member
One thing that I should mention is that it will not work properly if you have any negative values in the row from columns C to AE inclusive. Although not obvious from the formula, the range used crosses over the column with the formula and continues into a number of columns to the right, empty cells, positive numbers or text will not affect the formula but negative numbers or errors will.
Thanks for informing about all the precautions that I need to take to get the right result.
To be honest, I also doubt if a more efficient formula would be possible for the given situation.

#### ABHISKV4

##### New Member

=AGGREGATE(14,6,INDEX(SUBTOTAL(9,OFFSET(Q3,,-SEQUENCE(,COLUMNS(C3:Q3),0),,-SEQUENCE(COLUMNS(C3:Q3)))),AGGREGATE(15,6,SEQUENCE(COLUMNS(C3:Q3))/(SUBTOTAL(9,OFFSET(Q3,,-SEQUENCE(,COLUMNS(C3:Q3),0),,-SEQUENCE(COLUMNS(C3:Q3))))>=S3),1),0),1)
This works like a charm, absolutely delighted once again with the solution, many thanks for your prompt help

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome.

In case you may be interested in avoiding the fairly long worksheet formulas &/or worrying about overlapping ranges, another approach would be to employ a user-defined function. Here is one.

VBA Code:
``````Function MinCellsVal(rng As Range, MinVal As Double) As Double
Dim a As Variant
Dim i As Long, j As Long, L As Long, Num As Long
Dim S As Double

a = Application.Index(rng.Value, 1, 0)
Num = UBound(a)
For L = 1 To Num
For i = 1 To Num - L + 1
S = 0
For j = i To i + L - 1
S = S + a(j)
Next j
If S >= MinVal And S > MinCellsVal Then MinCellsVal = S
Next i
If MinCellsVal > 0 Then Exit For
Next L
End Function``````

ABHISKV4 1.xlsm
ABCDEFGHIJKLMNOPQRST
1
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
3Sales Qty -->15595109159688673119102744888941951601625813880
Columns to sum
Cell Formulas
RangeFormula
R3R3=SUM(C3:Q3)
S3S3=ROUNDUP(50%*R3,0)
T3T3=MinCellsVal(C3:Q3,S3)

#### ABHISKV4

##### New Member
In case you may be interested in avoiding the fairly long worksheet formulas &/or worrying about overlapping ranges, another approach would be to employ a user-defined function. Here is one.
I can't thank you enough in words, that's just awesome. Glad and lucky to be part of such a helpful community

#### Peter_SSs

##### MrExcel MVP, Moderator
I can't thank you enough in words,
You just have.

Replies
5
Views
37
Replies
6
Views
163
Replies
6
Views
166
Replies
4
Views
140
Replies
18
Views
129

1,137,353
Messages
5,680,994
Members
419,948
Latest member
Sbakker1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back