Excel Formula Countifs + Unique Values

[ItalianPlatinum]

Hello - looking for a formula or VBA to accomplish the below. something that will count unique values by a criteria I define

A	B	C	D	F	G	H	I	J
XX	XX	AA	ABC	XX	XX	XX	XX	XX
XX	XX	AA	ABC	XX	XX	XX	XX	XX
XX	XX	AA	DEF	XX	XX	XX	XX	XX
XX	XX	BB	ABC1	XX	XX	XX	XX	XX
XX	XX	BB	DEF1	XX	XX	XX	XX	XX
XX	XX	CC	ABC2	XX	XX	XX	XX	XX
XX	XX	CC	ABC2	XX	XX	XX	XX	XX
XX	XX	CC	ABC2	XX	XX	XX	XX	XX
XX	XX	CC	DEF2	XX	XX	XX	XX	XX
	Unique	AA	2
	Unique	BB	2
	Unique	CC	2

 

[ItalianPlatinum]

Try

Sub UniqueCount_v4()
  Dim d As Object
  Dim a As Variant, Ky As Variant
  Dim lastrw As Long, i As Long
  Dim s As String
  Dim wsDest As Worksheet
 
  Const ResultWorkbook As String = "Results.xlsx"  '<- Edit to suit
  Const ResultWorksheet As String = "abc"   '<- Edit to suit
  Const ResultTopLeft As String = "K1"          '<- Where you want the results
  Const CritColValCol As String = "8 5"         '<- Criteria column & Values column in that order. Edit to suit.
 
  With ActiveSheet
    lastrw = .Cells(.Rows.Count, CLng(Split(CritColValCol)(0))).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("row(2:" & lastrw & ")"), Split(CritColValCol))
    Set d = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(a)
      s = "|" & a(i, 2) & "|"
      If InStr(1, d(a(i, 1)), s, 1) = 0 Then d(a(i, 1)) = d(a(i, 1)) & s
    Next i
    ReDim a(1 To d.Count, 1 To 2)
    i = 0
    For Each Ky In d.Keys()
      i = i + 1
      a(i, 1) = Ky: a(i, 2) = UBound(Split(d(Ky), "||")) + 1
    Next Ky
   
  End With
  With Workbooks(ResultWorkbook).Sheets(ResultWorksheet).Range(ResultTopLeft)
    .Resize(, 2).Value = Array("Criteria", "Count")
    .Offset(1).Resize(d.Count, 2).Value = a
  End With
End Sub

PERFECT! Works great. Thank you so much.

 

[Peter_SSs]

Cheers. Thanks for the confirmation.

 

[ItalianPlatinum]

Cheers. Thanks for the confirmation.

Hey Peter - Was hoping you could assist me on this code. It works great but it sees to clock and doubled my codes processing time. Do you have any suggestions?

 

[Peter_SSs]

There is a lot of work to be done to get the results. Sorry, a faster way to get them is not apparent to me.