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

 

[B___P]

My formula

=PERMUT(14,14)/PRODUCT(PERMUT({3,1,3,6,1}, {3,1,3,6,1}))

or

=FACT(14)/PRODUCT(FACT({3,1,3,6,1}))

 

[Kishan]

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

Hi shg, too many combinations did not imagine</SPAN></SPAN>

Thank you for your help and time</SPAN></SPAN>

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

 

[Kishan]

My formula

=PERMUT(14,14)/PRODUCT(PERMUT({3,1,3,6,1}, {3,1,3,6,1}))

or

=FACT(14)/PRODUCT(FACT({3,1,3,6,1}))

Hi B___P, "FACT" formulas is neat, I am surprised to see that row 17 post#7 could be written in the 3.363.360 ways. But it is true as formula result amazing.</SPAN></SPAN>

Thank you for your help and time </SPAN></SPAN>

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

 

[shg]

=FACT(14)/PRODUCT(FACT({3,1,3,6,1}))

That's equivalent mathematically but not computationally:

	A​	B​	C​	D​
1​		Repeats​
2​		9​	0​	C2: =SUM(B$2:B2) - B2
3​		12​	9​
4​		18​	21​
5​		12​	39​
6​		11​	51​
7​		14​	62​
8​		9​	76​
9​		11​	85​
10​		8​	96​
11​		12​	104​
12​		12​	116​
13​		11​	128​
14​		12​	139​
15​		8​	151​
16​		12​	159​
17​	Total​	171​
18​
19​			1.352E+187​	C19: {=PRODUCT(COMBIN(B17 - C2:C16, B2:B16))}
20​			#NUM!​	C20: {=FACT(B17)/PRODUCT(FACT(B2:B16))}

 

[Kishan]

That's equivalent mathematically but not computationally:

	A​	B​	C​	D​
1​		Repeats​
2​		9​	0​	C2: =SUM(B$2:B2) - B2
3​		12​	9​
4​		18​	21​
5​		12​	39​
6​		11​	51​
7​		14​	62​
8​		9​	76​
9​		11​	85​
10​		8​	96​
11​		12​	104​
12​		12​	116​
13​		11​	128​
14​		12​	139​
15​		8​	151​
16​		12​	159​
17​	Total​	171​
18​
19​			1.352E+187​	C19: {=PRODUCT(COMBIN(B17 - C2:C16, B2:B16))}
20​			#NUM!​	C20: {=FACT(B17)/PRODUCT(FACT(B2:B16))}

<TBODY>
</TBODY>

Hi shg, thank you for explanation that formula fails in some situationes, why I said "FACT" formulas is neat because I tried your formula post#10 to understand but did not workout the first values where form are generated please can you explain values shown in red =product(combin(14-{0,3,4,7,13</SPAN></SPAN>}, {3,1,3,6,1}))</SPAN></SPAN>

Thank you</SPAN></SPAN>

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

 

[shg]

Choose 3 of 14 for the first number.

There's 11 left: Choose 1

There 10 left: Choose 3

There's 7 left: Choose 6

There's 1 left: Choose 1

Multiply all those together.

There's no need to quote my posts back to me.

 

[Kishan]

@shg, got it, thank you for explaining in details </SPAN></SPAN>

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