Count unique values excel formula with Blanks - Countif shows a character limit error

Question

I have to check if in a vertical array of 9 cells, whether any two or more (upto all 9 cells) comments are the same, rest may be blank. If they're the same, shows TRUE else FALSE.

The formula I was working with is given below and works perfectly. I used Sumproduct and CountIF together to solve this, but since CountIF has a 255 character limit, any comment above the character limit gives me a #N/A error.

=SUMPRODUCT(($DU$3:$DU$11<>"")/COUNTIF($DU$3:$DU$11,$DU$3:$DU$11&""))=1

I am looking for the CountIF workaround in my listed formula which does not have a 255 character limit and can have larger text. Thank you for your help.

JvdV JvdV · Accepted Answer · 2019-10-04T12:52:52

Got a formula that should do the trick:

=SUM(IF(DU3:DU11<>"",IF(DU3:DU11=TRANSPOSE(DU3:DU11),1,0),0))=COUNTA(DU3:DU11)

Note: It's an array formula and needs to be confirmed through CtrlShiftEnter

It will return FALSE if there are any duplicate values. It will return TRUE if no duplicates are used.

Another option would be to use a UDF, possibly the below:

Function UniqueVals(rng As Range) As Boolean

Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
Dim arr As Variant, x As Long
arr = rng.Value
For x = LBound(arr) To UBound(arr)
    If arr(x, 1) <> "" Then
        If dict.Exists(arr(x, 1)) Then
            UniqueVals = False
            Exit For
        Else
            UniqueVals = True
            dict.Add arr(x, 1), 1
        End If
    End If
Next x

End Function

Call the above like =UniqueVals(DU3:DU11) and it will return TRUE if all values are unique. It will return FALSE if there are any duplicates.

Stumbled onto this old post on SO too.

Count unique values excel formula with Blanks - Countif shows a character limit error

1 Answers