# Require change to limit exact sum output combinations

#### Kishan

##### Well-known Member
Using Excel2000

Hi,

Till now I was using "pgc01 code" which is amazing, I found code under this link
https://www.mrexcel.com/forum/excel-questions/277924-combination-help-5.html#post1424848

I require modifications code only output Permutation Repetition with a desire sums

For example...
In the cells B6:B10 there are numbers 0,1,2,3,4,5 I need all Permutation Repetition could be find with SUM = 43. Is it possible?
Layout is not important could be the any way depending on the new or modified code

Data example....

Book1
ABCDEFGHIJKLMNOPQRS
1P14*3333334242444143
2CombinationsFALSE*2243232334533443
3RepetitionTRUE*5214323234432543
4***5353254411134243
5Set Col B5 Down0*4245432333134243
6*1*2422354343431343
7*2*3142343355144143
8*3*3222541534323443
9*4*2353454144112443
10*5*5314344442311443
11**3351224431533443
12**5045353213512443
13**2335142322543443
14**2334452232233543
15**1313514345334343
164242442333334243
171413455233432343
18
19
Sheet1

Regards,
Kishan

Last edited:

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
If you just need to know how many there are rather than list them (there are over 2 billion),

 A​ B​ C​ 1​ Sum​ Ways​ ​ 2​ 0​ 1​ B2:B72: {=polyExp({1,1,1,1,1,1}, 14)} 3​ 1​ 14​ 4​ 2​ 105​ 5​ 3​ 560​ 6​ 4​ 2,380​ 7​ 5​ 8,568​ 8​ 6​ 27,118​ 9​ 7​ 77,324​ 10​ 8​ 202,020​ 11​ 9​ 489,580​ 12​ 10​ 1,110,746​ 13​ 11​ 2,376,192​ 14​ 12​ 4,820,543​ 15​ 13​ 9,316,594​ 16​ 14​ 17,218,995​ 17​ 15​ 30,529,240​ 18​ 16​ 52,063,571​ 19​ 17​ 85,593,522​ 20​ 18​ 135,917,523​ 21​ 19​ 208,814,424​ 22​ 20​ 310,829,610​ 23​ 21​ 448,854,900​ 24​ 22​ 629,486,676​ 25​ 23​ 858,182,052​ 26​ 24​ 1,138,276,503​ 27​ 25​ 1,469,971,230​ 28​ 26​ 1,849,435,329​ 29​ 27​ 2,268,186,480​ 30​ 28​ 2,712,905,781​ 31​ 29​ 3,165,803,382​ 32​ 30​ 3,605,583,723​ 33​ 31​ 4,008,970,980​ 34​ 32​ 4,352,660,949​ 35​ 33​ 4,615,482,690​ 36​ 34​ 4,780,499,451​ 37​ 35​ 4,836,766,584​ 38​ 36​ 4,780,499,451​ 39​ 37​ 4,615,482,690​ 40​ 38​ 4,352,660,949​ 41​ 39​ 4,008,970,980​ 42​ 40​ 3,605,583,723​ 43​ 41​ 3,165,803,382​ 44​ 42​ 2,712,905,781​ 45​ 43​ 2,268,186,480​ 46​ 44​ 1,849,435,329​ 47​ 45​ 1,469,971,230​ 48​ 46​ 1,138,276,503​ 49​ 47​ 858,182,052​ 50​ 48​ 629,486,676​ 51​ 49​ 448,854,900​ 52​ 50​ 310,829,610​ 53​ 51​ 208,814,424​ 54​ 52​ 135,917,523​ 55​ 53​ 85,593,522​ 56​ 54​ 52,063,571​ 57​ 55​ 30,529,240​ 58​ 56​ 17,218,995​ 59​ 57​ 9,316,594​ 60​ 58​ 4,820,543​ 61​ 59​ 2,376,192​ 62​ 60​ 1,110,746​ 63​ 61​ 489,580​ 64​ 62​ 202,020​ 65​ 63​ 77,324​ 66​ 64​ 27,118​ 67​ 65​ 8,568​ 68​ 66​ 2,380​ 69​ 67​ 560​ 70​ 68​ 105​ 71​ 69​ 14​ 72​ 70​ 1​

I can post the function if that works for you.

If you just need to know how many there are rather than list them (there are over 2 billion),</SPAN></SPAN>
I can post the function if that works for you.
</SPAN></SPAN>
Hi shg, I am amazed to know there could be 2,268,186,480 combinations with sum 43</SPAN></SPAN>

</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>

Code:
``````Function PolyExp(Poly As Variant, iExp As Long, Optional iUB As Long = -1) As Variant
' shg 2014

Else
PolyExp = CVErr(xlErrValue)
End If
End Function

Function adPolyExp(ad() As Double, iExp As Long, Optional ByVal iUB As Long = -1) As Double()
' shg 2014
' Returns the exponent of the polynomial

' VBA only

Dim i             As Long

If iExp < 1 Then Stop

For i = 2 To iExp
Next i

End Function

' shg 2014
' Returns the product of two polynomials

Dim iUB1          As Long
Dim iUB2          As Long
Dim i             As Long
Dim j             As Long

If iMAX = -1 Then iMAX = iUB1 + iUB2
If iMAX > iUB1 + iUB2 Then iMAX = iUB1 + iUB2

For i = 0 To iUB1
For j = 0 To iUB2
If i + j > iMAX Then Exit For
Next j
Next i
End Function

Function bMake1D(av As Variant, ad() As Double) As Boolean
' shg 2014
' VBA only

' Returns a 1D 0-based array of the values in av, which can be a
' column or row vector, a 1D array, or a scalar

' Reuires NumDim

Dim rArea         As Range
Dim cell          As Range
Dim nOut          As Long
Dim i             As Long
Dim iLB1          As Long
Dim iUB1          As Long
Dim iLB2          As Long
Dim iUB2          As Long

If TypeOf av Is Range Then av = av.Value2

Select Case NumDim(av)
Case 0
Select Case VarType(av)
Case vbDouble, vbLong, vbInteger, vbSingle, vbByte
bMake1D = True
End Select

Case 1
iLB1 = LBound(av)
iUB1 = UBound(av)

ReDim ad(0 To iUB1 - iLB1)
For i = iLB1 To iUB1
If VarType(av(i)) = vbDouble Then
Else
GoTo Oops
End If
Next i
bMake1D = True

Case 1.5, 2
iLB1 = LBound(av, 1)
iUB1 = UBound(av, 1)
iLB2 = LBound(av, 2)
iUB2 = UBound(av, 2)

If iUB1 <> iUB1 And iUB2 <> iLB2 Then
GoTo Oops

ElseIf iLB2 = iUB2 Then
' column vector
ReDim ad(0 To iUB1 - iLB1)

For i = iLB1 To iUB1
Select Case VarType(av(i, iLB2))
Case vbDouble, vbLong, vbInteger, vbSingle, vbByte
ad(i - iLB1) = av(i, iLB2)
Case Else
GoTo Oops
End Select
Next i
bMake1D = True

Else    ' iLB1 = iUB1
' row vector
ReDim ad(0 To iUB2 - iLB2)

For i = iLB2 To iUB2
Select Case VarType(av(iLB1, i))
Case vbDouble, vbLong, vbInteger, vbSingle, vbByte
ad(i - iLB2) = av(iLB1, i)
Case Else
GoTo Oops
End Select
Next i
bMake1D = True

End If
End Select

Oops:
End Function

Function NumDim(av As Variant) As Double
' shg 2014
' Returns
'   0 for a scalar or single-cell range
'   1 for a 1D array
'   1.5 for a row or column-vector range
'   n for an n-dimensional array

Dim i             As Long

If TypeOf av Is Range Then
If av.Cells.Count = 1 Then
NumDim = 0
ElseIf av.Columns.Count = 1 Or av.Rows.Count = 1 Then
NumDim = 1.5
Else
NumDim = 2
End If

ElseIf IsArray(av) Then
On Error GoTo AlmostDone

For NumDim = 0 To 6000
i = LBound(av, NumDim + 1)
Next NumDim
AlmostDone:
Err.Clear
If NumDim = 2 Then
If LBound(av, 1) = UBound(av, 1) Or _
LBound(av, 2) = UBound(av, 2) Then NumDim = 1.5
End If
End If
End Function``````

Code:
``````Function PolyExp(Poly As Variant, iExp As Long, Optional iUB As Long = -1) As Variant
' shg 2014

Err.Clear
If NumDim = 2 Then
If LBound(av, 1) = UBound(av, 1) Or _
LBound(av, 2) = UBound(av, 2) Then NumDim = 1.5
End If
End If
End Function``````
Hi shg, I liked your function it is calculating all possible combinations with each of sum through 0 to 70 lovely function </SPAN></SPAN>

I will see now what condition can be applied to reduce the number of combinations
</SPAN></SPAN>

Thank you for posting. I appreciate your help and time
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>

You're welcome.

Hi shg, if I use 14 times any of "0,1,2,3,4,5" once in all the 14 positions as per function we can have 1 combination with sum 0 & 1 combination with sum 70, as shown in the row1 used 0=14 Times & in the row2 used 5=14 Times</SPAN></SPAN>

Instead using all "0,1,2,3,4,5" once in the row can be use multiple times.</SPAN></SPAN>
For example: number 0=0 Time, 1=1 Time, 2=2 Times, 3=6 Times, 4=5 Times & 5=0 Time. As shown in the row8</SPAN></SPAN>

Example data </SPAN></SPAN>

Book1
ABCDEFGHIJKLMNOPQRSTUVW
1Sumc1c2c3c4c5c6c7c8c9c10c11c12c13c14012345
21000000000000001400000
370555555555555550000014
4
5
6
7Sumc1c2c3c4c5c6c7c8c9c10c11c12c13c14012345
84333333342424441012650
94322432323345334004631
104352143232344325014432
114353532544111342032333
124342454323331342013541
134324223543434313013541
144331423433551441031442
154332225415343234014432
164323534541441124032252
174353143444423114031361
184333512244315334022532
194350453532135124122324
204323351423225434014432
214323344522322335005522
224313135143453343030632
234342424423333342004550
244314134552334323022532
25
26
Sheet3

If each row has different conditions as specified in cells Q8:V24, will it be possible to calculated by the function how much combinations could be made by using different conditions.</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

Last edited:
Each of the 14 numbers can be 0 through 5, so the number of possibilities is 6^14, which is 78,364,164,096, which is what all those numbers add up to.

Each of the 14 numbers can be 0 through 5, so the number of possibilities is 6^14, which is 78,364,164,096, which is what all those numbers add up to.
Hi shg, you are correct I agree, but my thought is how much combinations could be made say for example in the row within 14 positions I use as follow.</SPAN></SPAN>

Number1=3 Times
</SPAN></SPAN>
Number2=1 Times
</SPAN></SPAN>
Number3=3 Times
</SPAN></SPAN>
Number4=6 Times
</SPAN></SPAN>
Number5=1 Times
</SPAN></SPAN>

Total numbers are 14 but some 1 time & other many time (0 is not used in the row) as shown row17, which total sum is 43, how much could be made applying these conditions to numbers
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>

Last edited:
=product(combin(14-{0,3,4,7,13}, {3,1,3,6,1}))

Replies
10
Views
692
Replies
13
Views
4K
Replies
10
Views
1K
Replies
10
Views
3K
Replies
1
Views
336

1,196,269
Messages
6,014,351
Members
441,816
Latest member
Klingon1960

### 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