Require change to limit exact sum output combinations

[Kishan]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	P	14	*	3	3	3	3	3	3	4	2	4	2	4	4	4	1	43
2	Combinations	FALSE	*	2	2	4	3	2	3	2	3	3	4	5	3	3	4	43
3	Repetition	TRUE	*	5	2	1	4	3	2	3	2	3	4	4	3	2	5	43
4	*	*	*	5	3	5	3	2	5	4	4	1	1	1	3	4	2	43
5	Set Col B5 Down	0	*	4	2	4	5	4	3	2	3	3	3	1	3	4	2	43
6	*	1	*	2	4	2	2	3	5	4	3	4	3	4	3	1	3	43
7	*	2	*	3	1	4	2	3	4	3	3	5	5	1	4	4	1	43
8	*	3	*	3	2	2	2	5	4	1	5	3	4	3	2	3	4	43
9	*	4	*	2	3	5	3	4	5	4	1	4	4	1	1	2	4	43
10	*	5	*	5	3	1	4	3	4	4	4	4	2	3	1	1	4	43
11	*		*	3	3	5	1	2	2	4	4	3	1	5	3	3	4	43
12	*		*	5	0	4	5	3	5	3	2	1	3	5	1	2	4	43
13	*		*	2	3	3	5	1	4	2	3	2	2	5	4	3	4	43
14	*		*	2	3	3	4	4	5	2	2	3	2	2	3	3	5	43
15	*		*	1	3	1	3	5	1	4	3	4	5	3	3	4	3	43
16				4	2	4	2	4	4	2	3	3	3	3	3	4	2	43
17				1	4	1	3	4	5	5	2	3	3	4	3	2	3	43
18
19
Sheet1

Thank you in advance


Regards,
Kishan

 

[shg]

If you just need to know how many there are rather than list them (there are over 2 billion),

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Sum​

[/td][td="bgcolor:#F3F3F3"]

Ways​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td]B2:B72: {=polyExp({1,1,1,1,1,1}, 14)}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"]

14​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

105​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

560​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

2,380​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]

8,568​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

6​

[/td][td="bgcolor:#E5E5E5"]

27,118​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

7​

[/td][td="bgcolor:#E5E5E5"]

77,324​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

8​

[/td][td="bgcolor:#E5E5E5"]

202,020​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

9​

[/td][td="bgcolor:#E5E5E5"]

489,580​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

10​

[/td][td="bgcolor:#E5E5E5"]

1,110,746​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

11​

[/td][td="bgcolor:#E5E5E5"]

2,376,192​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

12​

[/td][td="bgcolor:#E5E5E5"]

4,820,543​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

13​

[/td][td="bgcolor:#E5E5E5"]

9,316,594​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

14​

[/td][td="bgcolor:#E5E5E5"]

17,218,995​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

15​

[/td][td="bgcolor:#E5E5E5"]

30,529,240​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

16​

[/td][td="bgcolor:#E5E5E5"]

52,063,571​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

17​

[/td][td="bgcolor:#E5E5E5"]

85,593,522​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

18​

[/td][td="bgcolor:#E5E5E5"]

135,917,523​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

19​

[/td][td="bgcolor:#E5E5E5"]

208,814,424​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

20​

[/td][td="bgcolor:#E5E5E5"]

310,829,610​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

21​

[/td][td="bgcolor:#E5E5E5"]

448,854,900​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

22​

[/td][td="bgcolor:#E5E5E5"]

629,486,676​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td]

23​

[/td][td="bgcolor:#E5E5E5"]

858,182,052​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td]

24​

[/td][td="bgcolor:#E5E5E5"]

1,138,276,503​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td]

25​

[/td][td="bgcolor:#E5E5E5"]

1,469,971,230​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td]

26​

[/td][td="bgcolor:#E5E5E5"]

1,849,435,329​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td]

27​

[/td][td="bgcolor:#E5E5E5"]

2,268,186,480​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td]

28​

[/td][td="bgcolor:#E5E5E5"]

2,712,905,781​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31​

[/td][td]

29​

[/td][td="bgcolor:#E5E5E5"]

3,165,803,382​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

32​

[/td][td]

30​

[/td][td="bgcolor:#E5E5E5"]

3,605,583,723​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

33​

[/td][td]

31​

[/td][td="bgcolor:#E5E5E5"]

4,008,970,980​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

34​

[/td][td]

32​

[/td][td="bgcolor:#E5E5E5"]

4,352,660,949​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

35​

[/td][td]

33​

[/td][td="bgcolor:#E5E5E5"]

4,615,482,690​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

36​

[/td][td]

34​

[/td][td="bgcolor:#E5E5E5"]

4,780,499,451​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

37​

[/td][td]

35​

[/td][td="bgcolor:#E5E5E5"]

4,836,766,584​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

38​

[/td][td]

36​

[/td][td="bgcolor:#E5E5E5"]

4,780,499,451​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

39​

[/td][td]

37​

[/td][td="bgcolor:#E5E5E5"]

4,615,482,690​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

40​

[/td][td]

38​

[/td][td="bgcolor:#E5E5E5"]

4,352,660,949​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

41​

[/td][td]

39​

[/td][td="bgcolor:#E5E5E5"]

4,008,970,980​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

42​

[/td][td]

40​

[/td][td="bgcolor:#E5E5E5"]

3,605,583,723​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

43​

[/td][td]

41​

[/td][td="bgcolor:#E5E5E5"]

3,165,803,382​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

44​

[/td][td]

42​

[/td][td="bgcolor:#E5E5E5"]

2,712,905,781​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

45​

[/td][td]

43​

[/td][td="bgcolor:#FFFF00"]

2,268,186,480​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

46​

[/td][td]

44​

[/td][td="bgcolor:#E5E5E5"]

1,849,435,329​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

47​

[/td][td]

45​

[/td][td="bgcolor:#E5E5E5"]

1,469,971,230​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

48​

[/td][td]

46​

[/td][td="bgcolor:#E5E5E5"]

1,138,276,503​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

49​

[/td][td]

47​

[/td][td="bgcolor:#E5E5E5"]

858,182,052​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

50​

[/td][td]

48​

[/td][td="bgcolor:#E5E5E5"]

629,486,676​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

51​

[/td][td]

49​

[/td][td="bgcolor:#E5E5E5"]

448,854,900​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

52​

[/td][td]

50​

[/td][td="bgcolor:#E5E5E5"]

310,829,610​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

53​

[/td][td]

51​

[/td][td="bgcolor:#E5E5E5"]

208,814,424​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

54​

[/td][td]

52​

[/td][td="bgcolor:#E5E5E5"]

135,917,523​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

55​

[/td][td]

53​

[/td][td="bgcolor:#E5E5E5"]

85,593,522​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

56​

[/td][td]

54​

[/td][td="bgcolor:#E5E5E5"]

52,063,571​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

57​

[/td][td]

55​

[/td][td="bgcolor:#E5E5E5"]

30,529,240​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

58​

[/td][td]

56​

[/td][td="bgcolor:#E5E5E5"]

17,218,995​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

59​

[/td][td]

57​

[/td][td="bgcolor:#E5E5E5"]

9,316,594​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

60​

[/td][td]

58​

[/td][td="bgcolor:#E5E5E5"]

4,820,543​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

61​

[/td][td]

59​

[/td][td="bgcolor:#E5E5E5"]

2,376,192​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

62​

[/td][td]

60​

[/td][td="bgcolor:#E5E5E5"]

1,110,746​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

63​

[/td][td]

61​

[/td][td="bgcolor:#E5E5E5"]

489,580​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

64​

[/td][td]

62​

[/td][td="bgcolor:#E5E5E5"]

202,020​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

65​

[/td][td]

63​

[/td][td="bgcolor:#E5E5E5"]

77,324​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

66​

[/td][td]

64​

[/td][td="bgcolor:#E5E5E5"]

27,118​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

67​

[/td][td]

65​

[/td][td="bgcolor:#E5E5E5"]

8,568​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

68​

[/td][td]

66​

[/td][td="bgcolor:#E5E5E5"]

2,380​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

69​

[/td][td]

67​

[/td][td="bgcolor:#E5E5E5"]

560​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

70​

[/td][td]

68​

[/td][td="bgcolor:#E5E5E5"]

105​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

71​

[/td][td]

69​

[/td][td="bgcolor:#E5E5E5"]

14​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

72​

[/td][td]

70​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[/table]

I can post the function if that works for you.

 

[Kishan]

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>

I appreciate your hard work. Please can I get the function?</SPAN></SPAN>

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

 

[shg]

Function PolyExp(Poly As Variant, iExp As Long, Optional iUB As Long = -1) As Variant
  ' shg 2014
  ' UDF wrapper for adPolyExp

  Dim ad()          As Double

  If bMake1D(Poly, ad) Then
    ad = adPolyExp(ad, iExp, iUB)
    PolyExp = WorksheetFunction.Transpose(ad)
  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
  Dim adO()         As Double

  If iExp < 1 Then Stop

  adO = ad
  For i = 2 To iExp
    adO = adPolyMult(adO, ad, iUB)
  Next i

  adPolyExp = adO
End Function

Function adPolyMult(ad1() As Double, ad2() As Double, Optional ByVal iMAX As Long = -1) As Double()
  ' shg 2014
  ' Returns the product of two polynomials

  Dim adO()         As Double
  Dim iUB1          As Long
  Dim iUB2          As Long
  Dim i             As Long
  Dim j             As Long

  iUB1 = UBound(ad1)
  iUB2 = UBound(ad2)
  
  If iMAX = -1 Then iMAX = iUB1 + iUB2
  If iMAX > iUB1 + iUB2 Then iMAX = iUB1 + iUB2
  
  ReDim adO(0 To iMAX)
  
  For i = 0 To iUB1
    For j = 0 To iUB2
      If i + j > iMAX Then Exit For
      adO(i + j) = adO(i + j) + ad1(i) * ad2(j)
    Next j
  Next i
  adPolyMult = adO
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
          ReDim ad(0 To 0)
          ad(0) = av
          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
          ad(i - iLB1) = av(i)
        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

 

[Kishan]

Function PolyExp(Poly As Variant, iExp As Long, Optional iUB As Long = -1) As Variant
  ' shg 2014
  ' UDF wrapper for adPolyExp

  Dim ad()          As Double

     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>

 

[shg]

You're welcome.

 

[Kishan]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Sum	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10	c11	c12	c13	c14		0	1	2	3	4	5
2	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0		14	0	0	0	0	0
3	70	5	5	5	5	5	5	5	5	5	5	5	5	5	5		0	0	0	0	0	14
4
5
6
7	Sum	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10	c11	c12	c13	c14		0	1	2	3	4	5
8	43	3	3	3	3	3	3	4	2	4	2	4	4	4	1		0	1	2	6	5	0
9	43	2	2	4	3	2	3	2	3	3	4	5	3	3	4		0	0	4	6	3	1
10	43	5	2	1	4	3	2	3	2	3	4	4	3	2	5		0	1	4	4	3	2
11	43	5	3	5	3	2	5	4	4	1	1	1	3	4	2		0	3	2	3	3	3
12	43	4	2	4	5	4	3	2	3	3	3	1	3	4	2		0	1	3	5	4	1
13	43	2	4	2	2	3	5	4	3	4	3	4	3	1	3		0	1	3	5	4	1
14	43	3	1	4	2	3	4	3	3	5	5	1	4	4	1		0	3	1	4	4	2
15	43	3	2	2	2	5	4	1	5	3	4	3	2	3	4		0	1	4	4	3	2
16	43	2	3	5	3	4	5	4	1	4	4	1	1	2	4		0	3	2	2	5	2
17	43	5	3	1	4	3	4	4	4	4	2	3	1	1	4		0	3	1	3	6	1
18	43	3	3	5	1	2	2	4	4	3	1	5	3	3	4		0	2	2	5	3	2
19	43	5	0	4	5	3	5	3	2	1	3	5	1	2	4		1	2	2	3	2	4
20	43	2	3	3	5	1	4	2	3	2	2	5	4	3	4		0	1	4	4	3	2
21	43	2	3	3	4	4	5	2	2	3	2	2	3	3	5		0	0	5	5	2	2
22	43	1	3	1	3	5	1	4	3	4	5	3	3	4	3		0	3	0	6	3	2
23	43	4	2	4	2	4	4	2	3	3	3	3	3	4	2		0	0	4	5	5	0
24	43	1	4	1	3	4	5	5	2	3	3	4	3	2	3		0	2	2	5	3	2
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>

Thank you in advance</SPAN></SPAN>

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

 

[shg]

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.

 

[Kishan]

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>

 

[shg]

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