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

 

[AhoyNC]

Here is one way:

Book1
	A	B	C	D	E	F	G	H	I
1	XX	XX	AA	ABC	XX	XX	XX	XX	XX
2	XX	XX	AA	ABC	XX	XX	XX	XX	XX
3	XX	XX	AA	DEF	XX	XX	XX	XX	XX
4	XX	XX	BB	ABC1	XX	XX	XX	XX	XX
5	XX	XX	BB	DEF1	XX	XX	XX	XX	XX
6	XX	XX	CC	ABC2	XX	XX	XX	XX	XX
7	XX	XX	CC	ABC2	XX	XX	XX	XX	XX
8	XX	XX	CC	ABC2	XX	XX	XX	XX	XX
9	XX	XX	CC	DEF2	XX	XX	XX	XX	XX
10
11		Unique	AA	2
12		Unique	BB	2
13		Unique	CC	2
Sheet1

Cell Formulas
Range	Formula
D11:D13	D11	{=SUM(IF(FREQUENCY(IF($C$1:$C$9=C11,MATCH($D$1:$D$9,$D$1:$D$9,0)),ROW($C$1:$C$9)-ROW($C$1)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[ItalianPlatinum]

Here is one way:

Book1
	A	B	C	D	E	F	G	H	I
1	XX	XX	AA	ABC	XX	XX	XX	XX	XX
2	XX	XX	AA	ABC	XX	XX	XX	XX	XX
3	XX	XX	AA	DEF	XX	XX	XX	XX	XX
4	XX	XX	BB	ABC1	XX	XX	XX	XX	XX
5	XX	XX	BB	DEF1	XX	XX	XX	XX	XX
6	XX	XX	CC	ABC2	XX	XX	XX	XX	XX
7	XX	XX	CC	ABC2	XX	XX	XX	XX	XX
8	XX	XX	CC	ABC2	XX	XX	XX	XX	XX
9	XX	XX	CC	DEF2	XX	XX	XX	XX	XX
10
11		Unique	AA	2
12		Unique	BB	2
13		Unique	CC	2
Sheet1

Cell Formulas
Range	Formula
D11:D13	D11	{=SUM(IF(FREQUENCY(IF($C$1:$C$9=C11,MATCH($D$1:$D$9,$D$1:$D$9,0)),ROW($C$1:$C$9)-ROW($C$1)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Am I able to use that if it references another workbook that could be closed?

 

[ItalianPlatinum]

Am I able to use that if it references another workbook that could be closed?

Alright got it to work with an external file. problem is my data has 500,000 lines and this formula is clocking big time. something i should have said earlier of what my actual data set was

 

[AhoyNC]

Maybe someone can help you with a VBA solution. Any formula solution will take time to run on that many rows.

 

[ItalianPlatinum]

Maybe someone can help you with a VBA solution. Any formula solution will take time to run on that many rows.

Thanks Ahoy. Guess it was worth a shot.

 

[Peter_SSs]

Haven't done much testing for speed on a large range but you could try this user-defined function and compare.
Function assumes 2 adjacent columns with criteria in the left column and values in the right column, like your sample.

Function CountUnique(rData As Range, sCrit As String) As Long
  Dim d As Object
  Dim a As Variant
  Dim i As Long

  Set d = CreateObject("Scripting.Dictionary")
  a = rData.Value
  For i = 1 To UBound(a)
    If a(i, 1) = sCrit Then d(a(i, 2)) = Empty
  Next i
  CountUnique = d.Count
End Function

ItalianPlatinum 2.xlsm
	A	B	C	D	E	F	G	H	I
1	XX	XX	AA	ABC	XX		Unique	AA	2
2	XX	XX	AA	ABC	XX		Unique	BB	2
3	XX	XX	AA	DEF	XX		Unique	CC	2
4	XX	XX	BB	ABC1	XX
5	XX	XX	BB	DEF1	XX
6	XX	XX	CC	ABC2	XX
7	XX	XX	CC	ABC2	XX
8	XX	XX	CC	ABC2	XX
9	XX	XX	CC	DEF2	XX
10
Sheet1

Cell Formulas
Range	Formula
I1:I3	I1	=CountUnique(C$1:D$500000,H1)

 

[ItalianPlatinum]

Haven't done much testing for speed on a large range but you could try this user-defined function and compare.
Function assumes 2 adjacent columns with criteria in the left column and values in the right column, like your sample.

Function CountUnique(rData As Range, sCrit As String) As Long
  Dim d As Object
  Dim a As Variant
  Dim i As Long

  Set d = CreateObject("Scripting.Dictionary")
  a = rData.Value
  For i = 1 To UBound(a)
    If a(i, 1) = sCrit Then d(a(i, 2)) = Empty
  Next i
  CountUnique = d.Count
End Function

ItalianPlatinum 2.xlsm
	A	B	C	D	E	F	G	H	I
1	XX	XX	AA	ABC	XX		Unique	AA	2
2	XX	XX	AA	ABC	XX		Unique	BB	2
3	XX	XX	AA	DEF	XX		Unique	CC	2
4	XX	XX	BB	ABC1	XX
5	XX	XX	BB	DEF1	XX
6	XX	XX	CC	ABC2	XX
7	XX	XX	CC	ABC2	XX
8	XX	XX	CC	ABC2	XX
9	XX	XX	CC	DEF2	XX
10
Sheet1

Cell Formulas
Range	Formula
I1:I3	I1	=CountUnique(C$1:D$500000,H1)

Pete - So this works great, I have one curveball though, if the criteria is in column C but the data to count uniquely is in column E how do I adjust for that? I tried to expand the formula but it doesn't yield correctly. I am trying to replicate this for another data set here the data isn't next to each other

 

[Peter_SSs]

Try this, enter the criteria range and values range separately as in this version of the function. It wouldn't even matter if the values range was to the left of the criteria range, so long as you still enter the criteria range as the first argument of the function.

Function CountUnique(rCrit As Range, rValues As Range, sCrit As String) As Variant
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long
  
  a = rCrit.Value
  b = rValues.Value
  If UBound(a) = UBound(b) Then
    Set d = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(a)
      If a(i, 1) = sCrit Then d(b(i, 1)) = Empty
    Next i
    CountUnique = d.Count
  Else
    CountUnique = CVErr(xlErrRef)
  End If
End Function

ItalianPlatinum 2.xlsm
	E	F	G	H	I	J	K	L	M
1	Values				Criteria			Criteria	Unique Count
2	ABC				AA			AA	2
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
Sheet1

Cell Formulas
Range	Formula
M2:M4	M2	=CountUnique(I$2:I$10,E$2:E$10,L2)

 

[ItalianPlatinum]

Sorry for not getting back sooner. This seems to be working. If I wanted the VBA to just produce the value is that a complete different undertaking? What I am seeing is its sourcing through a whole lot of data for the formula so seeing some performance latency. seeing i have it sourcing a data file with 400,000 lines