vba code to count the unique values based on criteria

abhi_jain80

New Member
Joined
May 31, 2021
Messages
27
Office Version
  1. 2016
Platform
  1. Windows
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
MN
1DateItem#
217/01/202110000036
316/02/202110000036
424/02/202110000038
504/03/202110000038
610000038
724/03/202110000039
828/03/202110000094
910000094
1020/04/202110000048
1110000094
1210/05/202110000060
1309/05/202110000060
1410000094
1525/04/202110000085
1615/03/202110000093
1708/04/202110000093
Purchases
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try this:

Dante Amor
MNO
1DateItem#
217/01/2021100000362
316/02/202110000036
424/02/202110000038
504/03/202110000038
610000038
724/03/202110000039
810000094
910000094
1020/04/202110000048
1110000094
1210/05/202110000060
1309/05/202110000060
1410000094
1525/04/202110000085
1615/03/202110000093
1708/04/202110000093
Hoja2
Cell Formulas
RangeFormula
O2O2=SUM(--((FREQUENCY(IF(M2:M17="",N2:N17),N2:N17))>0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Try this:

Dante Amor
MNO
1DateItem#
217/01/2021100000362
316/02/202110000036
424/02/202110000038
504/03/202110000038
610000038
724/03/202110000039
810000094
910000094
1020/04/202110000048
1110000094
1210/05/202110000060
1309/05/202110000060
1410000094
1525/04/202110000085
1615/03/202110000093
1708/04/202110000093
Hoja2
Cell Formulas
RangeFormula
O2O2=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.
 
Upvote 0
How about:

VBA Code:
Sub unique_values()
  MsgBox Evaluate("=SUM(--((FREQUENCY(IF(M2:M17="""",N2:N17),N2:N17))>0))")
End Sub
 
Upvote 0
Solution
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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