# A combination of three tables gives the amount 20

#### bines53

##### Active Member
Hello friends,

There are three columns, each column, 10 values, need to find the formula, which enable combinations, one value from column to give the amount to 20.
The rule is possible to use the value in Column A, only once, and in two other columns, there is no limit.
Without volatile functions,
The result should be 7 combinations.
I came to the result, with 108 characters without character {} at the beginning and at the end of the formula.
Some values are highlighted for illustration.

Good luck !

David

#### Attachments

• 8.8 KB Views: 48
Last edited:

#### bines53

##### Active Member
I made a small change in the formula, and I got to 100 characters.

#### bines53

##### Active Member
Hello friends,

I made a small change in the formula, and I got to 84 characters.

Next Monday, I will present the formula, and another formula to give, how many possible combinations, in any amount, from the formula.

David.

#### John Jairo V

##### Well-Known Member
Maybe with this?

=COUNT(1/MMULT(N(SMALL(B2:B11+TRANSPOSE(C2:C11),COLUMN(A:CV))+A2:A11=20),ROW(1:100)))

Blessings!

• XOR LX and NARAYANK991

#### bines53

##### Active Member
Hi John,

Very similar to mine, the solution gives the desired result, a number of characters equal to mine, just do not see any value in column A, include in results 7.

Very nice .

David

#### bines53

##### Active Member
Hello friends,

This week passed very quickly,

The formula,

=COUNT(IF(MATCH(20-A2:A11,SMALL(B2:B11+TRANSPOSE(C2:C11),ROW(A1:A100)),0),A2:A11,""))

enter, CTRL +SHIFT +ENTER.(CSE)

Put the cursor on the COUNT and pressing the function button, see

{2;14;#N/A;#N/A;10;12;4;6;8;#N/A},
Get seven values in column A gives the combinations, the sum of 20.
If you do not want them values, then it is possible shorter function.

=COUNT(MATCH(20-A2:A11,SMALL(B2:B11+TRANSPOSE(C2:C11),ROW(A1:A100))))
enter, CTRL +SHIFT +ENTER

Now,
We have a table, three columns, each column has 10 lines, which means there are 1000 combinations, 10 ^ 3, we want to know how many combinations there are, if the sum is 9, or any other amount, the formula is

=SUM(--(SMALL(\$B\$2:\$B\$11+TRANSPOSE(\$C\$2:\$C\$11),ROW(A1:A100))+TRANSPOSE(A2:A11)=9))

enter, CTRL +SHIFT +ENTER

I hope you enjoyed, and thank you for your cooperation.

Soon I'll make something more challenging.

David

• r1c1 and Asheesh