Count Unique Rows in an Excel Spreadsheet that Meet Certain Criteria

Question

I want to count all the unique values in an Excel column that contain a certain string. For instance, say I have this in my column:

strA
strB
strA
strC
diffA
diffB
strC

If I give this theoretical function that I'm writing a value of "str," it will count the unique values containing "str", and give me back the number 3.

Any suggestions? The web is full of half-answers to this problem, but I've spent an hour trying to find a clear solution, so I thought StackOverflow might benefit from the question.

Answer 1

Option Explicit

Sub Test()
    GetCount 1, "str"
End Sub

Sub GetCount(colNum As Integer, str As String)
    Dim row As Integer
    Dim dict
    Set dict = CreateObject("Scripting.Dictionary")

    row = 1

    Dim key As String
    While Cells(row, colNum) <> ""
        key = Cells(row, colNum)
        If InStr(key, str) And Not dict.Exists(key) Then
            dict.Add key, key
        End If

        row = row + 1
    Wend

    MsgBox ("the count is " & dict.Count)

End Sub

When run with your input sample, this gives 3. The terminating condition for the loop is the first empty cell found in a column. You can adjust that if the logic isn't what you want.

You need to add a reference to Microsoft Scripting Runtime in the VBA section for this macro to compile and run.

Answer 2

=SUM(1/COUNTIF(A1:A7,A1:A7)*(LEFT(A1:A7,3)="str"))

Enter with Control+Shift+Enter, not just Enter, because it's an array formula. Typically to count the number of unique items in the list, you would array enter

=SUM(1/COUNTIF(A1:A7,A1:A7))

I multiplied the COUNTIF portion by another criterion (begins with str) so that those that don't begin with the string are treated as zero. If you're new to array formulas, see

http://www.dailydoseofexcel.com/archives/2004/04/05/anatomy-of-an-array-formula/

Answer 3

I have been too slow, but I got a different solution:

Put the string to search in B1 then Enter as an Array Formula:

=SUM(NOT(ISERROR(SEARCH(B1;A1:A7)))*(1/COUNTIF(A1:A7,A1:A7))