Modifcation in UDF to include logic symbols

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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.

ABCDEFG
1Datesvalu1valu2Conditions:
208/12/2016146.3727.07DatesValu1Valu2
316/03/2016100.4565.5116/03/2016<=146.37>=27.07
416/03/2016146.3727.07
527/12/2016601.5163.252
=DCOUNTA(A1:C20,B1,E2:G3)
617/05/2017146.3727.07
718/05/2016179.3154.54
802/02/2016706.8877.51
901/02/2017459.7451.25
1030/09/2015897.4442.29
1116/03/2016409.7888.12
1210/03/2017977.6733.73
1313/05/2017867.8381.12
1413/08/2015576.1316.95
1531/03/2017815.4480.83
1622/12/2016243.3635.92
1719/07/2016994.8552.29
1808/08/2015588.1222.67
1918/05/2016559.3144.79
2014/05/2016354.1147.03

<tbody>
</tbody>
 
Last edited:
Upvote 0
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:
Upvote 0
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)

JKLMN
6DatesValu1Valu1Valu2REGISTERED
716/03/2016>=27.07<=146.37>=5TRUE

<tbody>
</tbody>

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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
The first formula's last argument (the conditions argument) indicates K3:L4, and thats the condition it checks, in this case:

Valu1Price
<=400>=27.07

<tbody>
</tbody>

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/201619944100.4565.51
3/10/201731545977.6733.73
12/22/201635863243.3635.92
3/16/201620577559.3144.79

<tbody>
</tbody>


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

<tbody>
</tbody>

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/201619944100.4565.51
3/16/201620577559.3144.79

<tbody>
</tbody>


Each formula uses different ranges as arguments for the conditions
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top