krishna334
Active Member
- Joined
- May 22, 2009
- Messages
- 391
Hi All,
I just created a UDF which counts the unique count of values in column A based on values in columns B,C and D
Screenshot below (sorry administrators if posting the link is not allowed; I dont know how to post it otherwise)
Imgur: The most awesome images on the Internet
Below is the UDF Code (I got this from some other site)
Function Unique3(Var As Range, Range1 As Range, Criteria1 As String, Range2 As Range, Criteria2 As String, Range3 As Range, Criteria3 As String)
Dim V As Variant
Dim C As Collection
Dim I As Long
V = Var
Set C = New Collection
On Error Resume Next
For I = 1 To UBound(V, 1)
If Range1.Cells(I, 1) = Criteria1 And Range2.Cells(I, 1) = Criteria2 And Range3.Cells(I, 1) = Criteria3 Then
C.Add V(I, 1), CStr(V(I, 1))
End If
Next I
On Error GoTo 0
Unique3 = C.Count
End Function
The code works fine if the Criteria1/2/3 are "A1" or "B1" etc as below
=Unique3(A2:A8,B2:B8,"A1",C2:C8,"B1",D2:D8,"C1")
But what modifications need to be done in the UDF so that it works even if i use logic operators in Criteria1/2/3 part.
I mean if i give formula like below ; it shud work
=Unique3(A2:A8,B2:B8,"<>A1",C2:C8,"=B1",D2:D8,"<>C1")
Thanks in advance!
I just created a UDF which counts the unique count of values in column A based on values in columns B,C and D
Screenshot below (sorry administrators if posting the link is not allowed; I dont know how to post it otherwise)
Imgur: The most awesome images on the Internet
Below is the UDF Code (I got this from some other site)
Function Unique3(Var As Range, Range1 As Range, Criteria1 As String, Range2 As Range, Criteria2 As String, Range3 As Range, Criteria3 As String)
Dim V As Variant
Dim C As Collection
Dim I As Long
V = Var
Set C = New Collection
On Error Resume Next
For I = 1 To UBound(V, 1)
If Range1.Cells(I, 1) = Criteria1 And Range2.Cells(I, 1) = Criteria2 And Range3.Cells(I, 1) = Criteria3 Then
C.Add V(I, 1), CStr(V(I, 1))
End If
Next I
On Error GoTo 0
Unique3 = C.Count
End Function
The code works fine if the Criteria1/2/3 are "A1" or "B1" etc as below
=Unique3(A2:A8,B2:B8,"A1",C2:C8,"B1",D2:D8,"C1")
But what modifications need to be done in the UDF so that it works even if i use logic operators in Criteria1/2/3 part.
I mean if i give formula like below ; it shud work
=Unique3(A2:A8,B2:B8,"<>A1",C2:C8,"=B1",D2:D8,"<>C1")
Thanks in advance!