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

 

[Peter_SSs]

If I wanted the VBA to just produce the value

Do you mean not have it as a function, but just a one-off calculation?

Sub UniqueCount()
  Dim d As Object
  Dim a As Variant, Ky As Variant
  Dim lastrw As Long, i As Long
  Dim s As String
  
  Const CritColValCol As String = "8 5" '<- Criteria column & Values column in that order. Edit to suit.
  Const ResultsTopLeft As String = "K1" '<- Where you want the results
  
  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
  With Range(ResultsTopLeft)
    .Resize(, 2).Value = Array("Criteria", "Count")
    .Offset(1).Resize(d.Count, 2).Value = a
  End With
End Sub

ItalianPlatinum 2.xlsm
	E	F	G	H	I	J	K	L
1	Values			Criteria			Criteria	Count
2	ABC			AA			AA	3
3	ABC			AA			BB	2
4	DEF			AA			CC	2
5	ABC1			BB
6	DEF1			BB
7	ABC2			CC
8	ABC2			CC
9	ABC2			CC
10	DEF2			CC
11	XXX			AA
Sheet1

 

[ItalianPlatinum]

Do you mean not have it as a function, but just a one-off calculation?

Sub UniqueCount()
  Dim d As Object
  Dim a As Variant, Ky As Variant
  Dim lastrw As Long, i As Long
  Dim s As String
 
  Const CritColValCol As String = "8 5" '<- Criteria column & Values column in that order. Edit to suit.
  Const ResultsTopLeft As String = "K1" '<- Where you want the results
 
  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
  With Range(ResultsTopLeft)
    .Resize(, 2).Value = Array("Criteria", "Count")
    .Offset(1).Resize(d.Count, 2).Value = a
  End With
End Sub

ItalianPlatinum 2.xlsm
	E	F	G	H	I	J	K	L
1	Values			Criteria			Criteria	Count
2	ABC			AA			AA	3
3	ABC			AA			BB	2
4	DEF			AA			CC	2
5	ABC1			BB
6	DEF1			BB
7	ABC2			CC
8	ABC2			CC
9	ABC2			CC
10	DEF2			CC
11	XXX			AA
Sheet1

if the data references another sheet or workbook could i just add With sheets in order to pull it? so issue i was seeing I was using INDIRECT formula in combination with your previous formula but using indirect the workbook had to be open to calculate so that is where my most recent request came from sorry for the multiple back and forths

 

[Peter_SSs]

I would expect using INDIRECT on such a large data set could have a significant impact on your sheet's performance?
My code could reference data on another sheet or in another workbook but that other workbook would still need to be open.

 

[ItalianPlatinum]

I would expect using INDIRECT on such a large data set could have a significant impact on your sheet's performance?
My code could reference data on another sheet or in another workbook but that other workbook would still need to be open.

I have not tested it yet. so the workbook will be open but at the end will be closed. using indirect it was calculating but then once the file was closed it would kick #Value! this is what made me think i need it to calculate in VBA and just put the value in the cell?

 

[Peter_SSs]

I have not tested it yet.

You mean my suggested code?

 

[ItalianPlatinum]

You mean my suggested code?

Correct the code i have not tested yet

 

[ItalianPlatinum]

How could i reference another sheet by using the below:

Const CritColValCol As String = "8 5" '<- Criteria column & Values column in that order. Edit to suit.

 

[Peter_SSs]

How could i reference another sheet

In the same workbook or a different (open) workbook?

 

[sp_ajaypal]

HI, I just want to understand your requirement. can you please explain. how we will get the output from given source data.

 

[sp_ajaypal]

HI ItalianPlatinum,

iam confused. Can you please explain your requirement & how we will get the output from given source data.