vba code to count the unique values based on criteria

[abhi_jain80]

Hi experts,

I am struggling to write a vba code to count the unique values based on criteria. Can somebody help me please?
Like, I have the data as shown in the mini-sheet attached. I need to find the unique number of items# from column2 if the Date is blank in column1. As per the sample data, the result should be 2. Thank in advance...

Spares Analytics ToolKit_2021.xlsb
	M	N
1	Date	Item#
2	17/01/2021	10000036
3	16/02/2021	10000036
4	24/02/2021	10000038
5	04/03/2021	10000038
6		10000038
7	24/03/2021	10000039
8	28/03/2021	10000094
9		10000094
10	20/04/2021	10000048
11		10000094
12	10/05/2021	10000060
13	09/05/2021	10000060
14		10000094
15	25/04/2021	10000085
16	15/03/2021	10000093
17	08/04/2021	10000093
Purchases

 

[DanteAmor]

Try this:

Dante Amor
	M	N	O
1	Date	Item#
2	17/01/2021	10000036	2
3	16/02/2021	10000036
4	24/02/2021	10000038
5	04/03/2021	10000038
6		10000038
7	24/03/2021	10000039
8		10000094
9		10000094
10	20/04/2021	10000048
11		10000094
12	10/05/2021	10000060
13	09/05/2021	10000060
14		10000094
15	25/04/2021	10000085
16	15/03/2021	10000093
17	08/04/2021	10000093
Hoja2

Cell Formulas
Range	Formula
O2	O2	=SUM(--((FREQUENCY(IF(M2:M17="",N2:N17),N2:N17))>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[abhi_jain80]

Try this:

Dante Amor
	M	N	O
1	Date	Item#
2	17/01/2021	10000036	2
3	16/02/2021	10000036
4	24/02/2021	10000038
5	04/03/2021	10000038
6		10000038
7	24/03/2021	10000039
8		10000094
9		10000094
10	20/04/2021	10000048
11		10000094
12	10/05/2021	10000060
13	09/05/2021	10000060
14		10000094
15	25/04/2021	10000085
16	15/03/2021	10000093
17	08/04/2021	10000093
Hoja2

Cell Formulas
Range	Formula
O2	O2	=SUM(--((FREQUENCY(IF(M2:M17="",N2:N17),N2:N17))>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks DanteAmor for your response. Actually I am looking for a formula in vba script.

 

[DanteAmor]

How about:

Sub unique_values()
  MsgBox Evaluate("=SUM(--((FREQUENCY(IF(M2:M17="""",N2:N17),N2:N17))>0))")
End Sub

 

[abhi_jain80]

How about:

Sub unique_values()
  MsgBox Evaluate("=SUM(--((FREQUENCY(IF(M2:M17="""",N2:N17),N2:N17))>0))")
End Sub

Many Thanks...this worked for me.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.