Determining number of possible combinations of a set of numbers

[gravanoc]

I have a formula that generates all the unique possible combinations from a set of numbers in two columns where the first column's number is always the first digit in the combination.

In the above example, there are 16 combinations fitting that description that are then filtered down to 9 after taking the unique combinations.. I would like to know if there is a formula that I can use that would generate that number, 9, for the possible combinations. Variations of using the combination functions (COMBIN, COMBINA) don't seem to do the trick. Thanks.

 

[Eric W]

In this case, the number of combinations is just the number of elements in column 1 times the number of elements in column 2. The number of unique values would be tougher to find.

Edit: the number of unique combinations would be the number of unique elements in column1 times the number of unique elements in column2.

 

[JGordon11]

delme1.xlsm
	A	B	C	D	E
1	Col 1	Col 2		Number Unique Combinations	if version doesn't include UNIQUE function
2	0	0		9	9
3	1	1
4	3	1
5	1	9
6	1
Sheet4

Cell Formulas
Range	Formula
D2	D2	=ROWS(UNIQUE(A2:A6))*ROWS(UNIQUE(B2:B5))
E2	E2	=SUM(1/COUNTIF(A2:A6,A2:A6))*SUM(1/COUNTIF(B2:B5,B2:B5))

 

[gravanoc]

delme1.xlsm
	A	B	C	D	E
1	Col 1	Col 2		Number Unique Combinations	if version doesn't include UNIQUE function
2	0	0		9	9
3	1	1
4	3	1
5	1	9
6	1
Sheet4

Cell Formulas
Range	Formula
D2	D2	=ROWS(UNIQUE(A2:A6))*ROWS(UNIQUE(B2:B5))
E2	E2	=SUM(1/COUNTIF(A2:A6,A2:A6))*SUM(1/COUNTIF(B2:B5,B2:B5))

Awesome. How did you figure it out, if you don't mind sharing?

 

[JGordon11]

As Eric wrote above, the number of unique combinations (order dependent - meaning e.g., 12 and 21 each count) is the product of the number of unique items in the first column and the number of unique items in the second column. In 365 the UNIQUE function returns the list of unique items from an array, the rows function returns the number of rows in an array which is the number of items for an nX1 array -so ROWS(UNIQUE(array1))*ROWS(UNIQUE(array2)) for vertical one column arrays returns the intended result. For versions without the UNIQUE function the SUM(1/COUNTIF construction does the same (if there is only one of an item it contributes 1 to the sum, if 2 each contribute 1/2, if 3 each 1/3 ... so always sums to the number of unique items).