Combinations Excel formula challenges

posted Jun 13, 2012, 8:58 AM by r   [ updated Jun 13, 2012, 9:27 AM ]
The real-life problem

Creating summary table, imagine that you have a database with columns:
Country -> you have 3 different country
Customer category -> 2 different category
Year -> 3 years

You would like to create a summary table for Country, Customer category and Year containing ALL the possible combinations, like a Pivot table, but in a fixed structure, using SUM-formulas (SUMIF, SUMIFS, SUMPRODUCT or array-entered SUM). So you have to write all the possible combinations of the three column, in total it will be 3*2*3=18

In general, you have to write combinations when choosing from different sets for each position.

Excel model with letters

Let's simplify the question and use letters instead of Country, Customer category and Year. In the below picture you can see what is the imput data and the expected result (now colum A to C for the 3 sets).

Challenge #1
It could be done with one formula in A2 which you drag to right and down, or with an array-entered formula.
Remember that the solution should be dynamic - if you add more letters to a set or add more sets the result should change accordingly.
On the second picture you can see that 2 more sets are added, so the result now contains 5 columns.

Challenge #2
Also in order to solve the first challenge we ask you a formula that solve the counting of combinations (in the pictures is in K12) ... also in this case the solution must be dynamic and without support.

You can download the file from bottom of the page by clicking on the small arrow on the right-hand side.
In the file formulas were fixed with the values.

Please post your formula-solutions, ideas, comments here:
Excel Hero Group on Linkedin

by Frankens Team



Some articles could help you:
Girone all'italiana (Round-robin tournament)
Combinazioni (semplici e con ripetizione) con Excel
Dismutazioni (o sconvolgimenti, o permutazioni complete) con Excel
Calcolo combinatorio con Excel
Calcolo Combinatorio con Excel - Il caso di Classe 2
Jun 13, 2012, 8:59 AM