# Counting UNIQUE values and list totals.

#### S.H.A.D.O.

##### Well-known Member
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.

Code:
``````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``````

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.

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:

Code:
``=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.

Code:
``=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!

Last edited:

Replies
0
Views
114
Replies
0
Views
103
Replies
6
Views
204
Replies
2
Views
261
Replies
3
Views
87

### Forum statistics

1,203,601
Messages
6,056,209
Members
444,850
Latest member
dancasta7 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back