How to Count Number of Unique Cells in Excel That Don't Contain Certain Characters

2
votes

I'm trying to count the number of unique cells in column C that don't have a "#" or "-" inside.

I have a column of cells that contain names. Some of these names are repeated, and some have characters such as "-" and/or "#" inside - I'm trying to exclude these cells from being counted.

I have 2 formulas that each does half of what I need, but I need to combine the 2 formulas to get the right answer:

This formula counts the number of unique cells (and takes care of blanks): =SUM(IF(COUNTIF(C4:C3689,C4:C3689)=0, "", 1/COUNTIF(C4:C3689,C4:C3689)))

This formula counts the number of cells that don't have a "#" or "-": =SUMPRODUCT(N(LEN(SUBSTITUTE(SUBSTITUTE(C4:C3689,"-",""),"#",""))=LEN(C4:C3689)))

Do you know how to combine the 2 formulas? If you know how to combine the two formulas in a different way (custom function or VBA) that would be great too.

Thanks.

2
excelvba

2 Answers

2
votes

This formula will count the number of different entries in the specified range, excluding any that contain # or -

=SUMPRODUCT((ISERR(SEARCH("#",C4:C3689))*ISERR(SEARCH("-",C4:C3689))*(C4:C3689<>""))/COUNTIF(C4:C3689,C4:C3689&""))

0
votes

Use a function like this:

=GetUniqueCount("C4:C3689")

Add the function code:

Function GetUniqueCount(rng As Range) As Variant
'Dim rng as Range
'Set rng = Range("C4:C3869") 'Modify as needed

Dim r as Range
Dim uniqueCount as Long
Dim dict as Object
Set dict = CreateObject("Scripting.Dictionary")
For each r in rng.Cells
    If Not dict.Exists(r.Value) Then
        If Instr(1, r.Value, "#") = 0 Then
            If Instr(1, r.Value, "-") = 0 Then
                dict(r) = ""
                uniqueCount = uniqueCount + 1
            End If
        End If
    End If
Next

GetUniqueCount = uniqueCount
Set dict = Nothing
End Function