Counting UNIQUE values and list totals.

[S.H.A.D.O.]

I have this Macro where the calculation does as I expect it to do.
What I really want it to do though is to only COUNT the UNIQUE values in the calculation. By that I mean that if the calculation calculates that there are say 5 number fours it only counts the fours as 1, and this applies to all the other numbers in the calculation.
Ideally, what I would like it to do is instead of it outputting the totals as in the Macro which covers the totals 35 to 422 is to only count the unique totals which will be from 1 to 48.

As an example, let's use the combination:

01, 12, 13, 15, 31, 44

This produces the totals as per the Macro of 274:

11, 01, 02, 16, 13, 12, 03, 18, 29, 14, 19, 31, 30, 32, 43

BUT in this case the answer should be 15 because there are 15 UNIQUE values.

I hope I have explained this clearly enough.

Option Explicit
Option Base 1

Const MinA As Integer = 1
Const MaxF As Integer = 49

Sub Test()
    Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long
    Dim n As Long
    Dim Sum(1 To 500) As Long
    Dim Total As Long
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    Range("A:B").ClearContents
    Range("A1").Select
    For n = LBound(Sum) To UBound(Sum)
        Sum(n) = 0
    Next n
    For A = MinA To MaxF - 5
        For B = A + 1 To MaxF - 4
            For C = B + 1 To MaxF - 3
                For D = C + 1 To MaxF - 2
                    For E = D + 1 To MaxF - 1
                        For F = E + 1 To MaxF
                            Sum(B - A + C - B + D - C + E - D + F - E + _
                                C - A + D - B + E - C + F - D + _
                                D - A + E - B + F - C + _
                                E - A + F - B + _
                                F - A) = _
                            Sum(B - A + C - B + D - C + E - D + F - E + _
                                C - A + D - B + E - C + F - D + _
                                D - A + E - B + F - C + _
                                E - A + F - B + _
                                F - A) + 1
                        Next F
                    Next E
                Next D
            Next C
        Next B
    Next A
    For n = LBound(Sum) To UBound(Sum)
        Total = Total + Sum(n)
        ActiveCell.Offset(0, 0).Value = n
        ActiveCell.Offset(0, 1).Value = Sum(n)
        ActiveCell.Offset(1, 0).Select
    Next n
    ActiveCell.Offset(0, 1).Value = Total
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Thanks in advance.

 

[S.H.A.D.O.]

I was just wondering, is there a Formula that I can use (using the same formula that is in the Macro, where A = cell M16...F = cell R16 for example) if the numbers were in cells M:R please.
I have tried to find something that adds UNIQUE or DISTINCT values but have not been able to find anything that does it within the formula itself. I also looked looked at FREQUENCY, but again, not within the formula itself.
Thanks in advance.

 

[S.H.A.D.O.]

Just an update to the Formula method.

I used helper columns T:AH to calculate the 15 differences that are involved, then I used the formula:

=SUMPRODUCT(1/COUNTIF(T16:AH16,T16:AH16))

...to calculate the UNIQUE values.

This formula also works and doesn't give a #DIV/0! error if the cells are empty.

=SUM(IF(FREQUENCY(T16:AH16,T16:AH16)>0,1))

If somebody knows of a way to calculate the 15 differences without helper columns that would be great!

Thanks in advance.