Thanks:  0
Likes:  0

# Thread: Modifcation in UDF to include logic symbols

1. ## Modifcation in UDF to include logic symbols

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
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")

2. ## Re: Modifcation in UDF to include logic symbols

You need a new function, the one you have only compares strings:

Range1.Cells(I, 1) = Criteria1 And Range2.Cells(I, 1) = Criteria2 And Range3.Cells(I, 1) = Criteria3

and counts if all are true; it won't take any logical operator.

If you don't need it to be a UDF and you can spare a few cells for the criteria conditions, you coud use DCOUNTA.

 A B C D E F G 1 Dates valu1 valu2 Conditions: 2 08/12/2016 146.37 27.07 Dates Valu1 Valu2 3 16/03/2016 100.45 65.51 16/03/2016 <=146.37 >=27.07 4 16/03/2016 146.37 27.07 5 27/12/2016 601.51 63.25 2 =DCOUNTA(A1:C20,B1,E2:G3) 6 17/05/2017 146.37 27.07 7 18/05/2016 179.31 54.54 8 02/02/2016 706.88 77.51 9 01/02/2017 459.74 51.25 10 30/09/2015 897.44 42.29 11 16/03/2016 409.78 88.12 12 10/03/2017 977.67 33.73 13 13/05/2017 867.83 81.12 14 13/08/2015 576.13 16.95 15 31/03/2017 815.44 80.83 16 22/12/2016 243.36 35.92 17 19/07/2016 994.85 52.29 18 08/08/2015 588.12 22.67 19 18/05/2016 559.31 44.79 20 14/05/2016 354.11 47.03

3. ## Re: Modifcation in UDF to include logic symbols

Thank you Leo for the reply.
I think DCOUNTA does not give what I am looking for.
In your example, i would say I would need the count of unique dates which is less than <=146.37 and >=27.07 and that would be 1 not 2 (i.e only one unique date 16/03/2016). This is what i am looking for.
Could you tell me how to modify the UDF so that it accepts logics also?

Thanks again for looking into this.

4. ## Re: Modifcation in UDF to include logic symbols

DCOUNTA works fine, there are two values, rows 3 and 4.

Also, you can have as many conditions as you want, for example:

DCOUNTA(A1:C20,B1,\$J\$6:\$N\$7)

 J K L M N 6 Dates Valu1 Valu1 Valu2 REGISTERED 7 16/03/2016 >=27.07 <=146.37 >=5 TRUE

Would check that all five conditions are met: Dates=16/03/2016, Valu1>=27.07, Valu1<=146.37, Valu2>=5,Valu2<=100, REGISTERED is TRUE.
The header row has to have the same name than the title in the column you want the condition to be met. ("Valu1" is different than "Value 1")

The only drawback that DCOUNTA and the other database functions have is that they need to have the conditions in a range of cells. sometimes, you just want a formula with all the conditions built in. If this is the case, I'm afraid it would take some time for me, since I am not the most proficient VBA coder... but I'm sure DCOUNT will work for you, unless you can't spare the cells needed for the conditions.

5. ## Re: Modifcation in UDF to include logic symbols

Hi Leo,
Can you send me a spreadsheet with this working...I think something is not right when I try it here.
Anyways Thank you so much for the help.

6. ## Re: Modifcation in UDF to include logic symbols

I sent you a file, as you requested, I hope you it helps you...

7. ## Re: Modifcation in UDF to include logic symbols

Leo,

Sorry for the late reply and thanks for sending the file.

The first formula gave an output as 4 (=DCOUNTA(B1:F20,B1,K3:L4)).
But there are only 3 unique dates i.e. 3/16/2016, 3/10/2016 and 12/22/2016.
I actually want the unique count (3) and not just the count (4) which matches the criteria.

Can DCOUNTA do that?

8. ## Re: Modifcation in UDF to include logic symbols

The first formula's last argument (the conditions argument) indicates K3:L4, and thats the condition it checks, in this case:

 Valu1 Price <=400 >=27.07

So, no dates are checked. This formula will count how many records have Valu1<=400 and Price>=27.07, these are the four records:

 Date Valu1 Valu2 Price Tax 3/16/2016 199 44 100.45 65.51 3/10/2017 315 45 977.67 33.73 12/22/2016 358 63 243.36 35.92 3/16/2016 205 77 559.31 44.79

Only the third formula checks the date, as the conditions argument is: J3:K4, which is:
 Date Valu1 3/16/2016 <=400

So this checks all those records that are on date 3/16/2016 that have Valu1 <= 400; which are two records:

 Date Valu1 Valu2 Price Tax 3/16/2016 199 44 100.45 65.51 3/16/2016 205 77 559.31 44.79

Each formula uses different ranges as arguments for the conditions

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•