# Count unique values with criteria

#### sharky12345

##### Well-known Member
I've got this formula which counts the unique values in column A on the condition that the text in column E matches the criteria NORTH:

=SUM(IF("NORTH"='AUG CR'!\$E\$2:\$E\$45453, 1/(COUNTIFS('AUG CR'!\$E\$2:\$E\$45453, "NORTH",'AUG CR'!\$A\$2:\$A\$45453, 'AUG CR'!\$A\$2:\$A\$45453)), 0))

I really need this to be done by VBA though as the workbook is already quite large and I'm trying to reduce the amount of formulas used.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Yongle

##### Well-known Member
Try this
VBA Code:
``````Sub CountUnique()
Dim arr As Variant, i As Long, coll As New Collection, itm As String
With ActiveSheet
arr = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 5)
End With
For i = 1 To UBound(arr, 1)
itm = UCase(arr(i, 5))
If itm = "NORTH" Then
itm = itm & arr(i, 1)
On Error Resume Next
On Error GoTo 0
End If
Next i
MsgBox coll.Count, , "UNIQUE COUNT"
End Sub``````

#### sharky12345

##### Well-known Member
That's just brilliant!

Thanks buddy!!

Replies
3
Views
54
Replies
8
Views
238
Replies
4
Views
76
Replies
10
Views
595
Replies
5
Views
89

1,127,566
Messages
5,625,545
Members
416,116
Latest member
Joemamasuka

### 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