BobA said:

As near as I can tell your formula gives the total

number of permutations.

Hmm, what gave it away: perhaps the explicit use of PERMUT(n,k)? ;-)

BobA said:

In three cells I have the following:

456 456 456 [....]

However there are only 6 unique combinations without any duplicates.

456

465

546

564

645

654

That is the only example for which you enumerated the combinations you are

interested in. Note that they are all permutations. But now I realize,

that was only by coincidence.

BobA said:

For example they could be:

12 145 5678

[....] In this example there are [...] 18 unique combinations. [....]

For example if I have:

345 2367

[....] There are [...] 11 unique combinations.

[....]

Think of a trifecta with the following ticket:

123

123

789

The 1,2 or 3 can come first or second but not third, and the

7,8 or 9 must come third.

[....] The correct number of combinations that I'm looking for is 18

It would be clearer if you enumerated the combinations for each example.

But I think I understand now: you want the (number of) unique combinations

of one digit from each cell, excluding duplicates.

Off-hand, I cannot think of an algebraic formula to calculate the number of

combinations directly. I must resort to actually enumerating the

combinations, then counting.

But that might be a good thing, since it might be prudent to look at the

combinations as well.

The VBA function below enumerates the combinations of 2 or 3 cells.

If the cells are A1:A3, simply writing =uniqCombo(A1:A3) returns the count.

But by selecting that number+1 cells in a column and array-entering

=uniqCombo(A1:A3) -- press ctrl+shift+Enter instead of just Enter -- the

function returns the count followed by the combinations.

So the output would like the following (copy, paste into text file, and open

it in Excel as a CSV file):

456,6,12,18,345,11,123,18

456,456,145,145,2367,32,123,127

456,465,5678,146,,36,789,128

,546,,147,,37,,129

,564,,148,,42,,137

,645,,156,,43,,138

,654,,157,,46,,139

,,,158,,47,,217

,,,215,,52,,218

,,,216,,53,,219

,,,217,,56,,237

,,,218,,57,,238

,,,245,,,,239

,,,246,,,,317

,,,247,,,,318

,,,248,,,,319

,,,256,,,,327

,,,257,,,,328

,,,258,,,,329

Or go to

https://app.box.com/s/jfnt8omxcuqmxftw1fth to see an image.

The VBA function....

Option Explicit

Function uniqCombo(rng As Range) As Variant

Dim i As Long, k As Long, j As Long, n As Long

Dim i1 As Long, i2 As Long, i3 As Long

Dim t1 As Long, t2 As Long, t3 As Long

Dim x1 As Long, x2 As Long

Dim r As Variant, s As String, x As Long

Dim nRng As Long, maxN As Long

' separate unique digits in each cell

nRng = rng.Count

If nRng <> 2 And nRng <> 3 Then

uniqCombo = CVErr(xlErrValue)

Exit Function

End If

ReDim t(1 To nRng, 0 To 10) As Long ' t(k,0) counts t(k,i)

For k = 1 To nRng

ReDim d(0 To 9) As Long ' reinit to zero

s = rng(k).Text

j = 0

For i = 1 To Len(s)

x = Mid(s, i, 1)

If d(x) <> 1 Then

d(x) = 1

j = j + 1

t(k, j) = x

End If

Next i

t(k, 0) = j

Next k

' enumerate unique combination of one digit

' from each cell, excluding duplicates

Select Case nRng

Case 2:

maxN = t(1, 0) * t(2, 0)

ReDim res(0 To maxN) As Variant ' res(0) is count

n = 0

For i1 = 1 To t(1, 0)

t1 = t(1, i1): x1 = t1 * 10

For i2 = 1 To t(2, 0)

t2 = t(2, i2)

If t2 <> t1 Then

n = n + 1

res(n) = x1 + t2

End If

Next i2

Next i1

Case 3:

maxN = t(1, 0) * t(2, 0) * t(3, 0)

ReDim res(0 To maxN) As Variant ' res(0) is count

n = 0

For i1 = 1 To t(1, 0)

t1 = t(1, i1): x1 = t1 * 100

For i2 = 1 To t(2, 0)

t2 = t(2, i2)

If t2 <> t1 Then

x2 = t2 * 10

For i3 = 1 To t(3, 0)

t3 = t(3, i3)

If t3 <> t1 And t3 <> t2 Then

n = n + 1

res(n) = x1 + x2 + t3

End If

Next i3

End If

Next i2

Next i1

End Select

' output result as column array

ReDim Preserve res(0 To n) As Variant

res(0) = n

uniqCombo = WorksheetFunction.Transpose(res)

End Function