Results 1 to 8 of 8

Modifcation in UDF to include logic symbols

This is a discussion on Modifcation in UDF to include logic symbols within the Excel Questions forums, part of the Question Forums category; Hi All, I just created a UDF which counts the unique count of values in column A based on values ...

  1. #1
    Board Regular krishna334's Avatar
    Join Date
    May 2009
    Location
    cochin
    Posts
    383

    Default 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
    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!

  2. #2
    Board Regular
    Join Date
    Jun 2017
    Posts
    68

    Default 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
    Last edited by Leo Skywalker; Jul 13th, 2017 at 05:19 PM.

  3. #3
    Board Regular krishna334's Avatar
    Join Date
    May 2009
    Location
    cochin
    Posts
    383

    Default 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.
    Last edited by krishna334; Jul 14th, 2017 at 03:32 AM.

  4. #4
    Board Regular
    Join Date
    Jun 2017
    Posts
    68

    Default 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.
    Last edited by Leo Skywalker; Jul 14th, 2017 at 11:00 AM.

  5. #5
    Board Regular krishna334's Avatar
    Join Date
    May 2009
    Location
    cochin
    Posts
    383

    Default 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. #6
    Board Regular
    Join Date
    Jun 2017
    Posts
    68

    Default Re: Modifcation in UDF to include logic symbols

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

  7. #7
    Board Regular krishna334's Avatar
    Join Date
    May 2009
    Location
    cochin
    Posts
    383

    Default 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. #8
    Board Regular
    Join Date
    Jun 2017
    Posts
    68

    Default 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
    Last edited by Leo Skywalker; Jul 17th, 2017 at 03:23 PM.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com