I have a several cells with strings such as this one:


I would like to count how many different people are named (i.e. how many unique values). The count for the string above would be 6. Note that the total amount of names in the strings can vary a lot. Some have only one name and the biggest one has 14 (where some can be duplicated). Some names have spaces in them (ex. "Peter Smith,Andrea,Carla Moore"), but we can rely on the commas to separate them.

I think the solution is to convert the string into an array and then use a combination of the formulas SUM, IF and FREQUENCY which I found online here, applied to that array.

However I don't know how to convert the string into an array (if that is the way to go).

  • Did you try something? – Toto Mar 3 '18 at 17:22
  • I found this here ozgrid.com/forum/forum/help-forums/excel-general/… and tried to use it but couldn't understand it. Then tried to split each string using the "Text to columns" function, and after that using that online tutorial I mention for counting values in an array, but I would have to enter the formula manually in each row otherwise the array doesn't change to next row. – PrincessLilly Mar 3 '18 at 17:42

Try this small User Defined Function:

Public Function PeopleKounter(s As String) As Long
    Dim DQ As String, c As Collection
    Set c = New Collection
    DQ = Chr(34)

    ary = Split(Replace(s, DQ, ""), ",")

    On Error Resume Next
        For Each a In ary
            c.Add a, CStr(a)
        Next a
    On Error GoTo 0

    PeopleKounter = c.Count
End Function

enter image description here

  • 1
    I've now added "Application.Volatile" at the top to allow this function to automatically refresh when the worksheet is recalculated. – PrincessLilly Mar 3 '18 at 22:12
  • @PrincessLilly Good idea..................................... – Gary's Student Mar 3 '18 at 22:34

This formula will iterate the "words" and only count the Unique.

=SUMPRODUCT(--(ISERROR(FIND(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW($1:$17)-1)*99+1,99)),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),1,(ROW($1:$17)-1)*99+1))))))

As it iterates it compares it to the current with all that came before with FIND(). And error is returned if not found and thus it is counted.

enter image description here

  • Clever use of SUMPRODUCT() – Gary's Student Mar 3 '18 at 20:08
  • Good option for someone who is looking to use a formula that's not user defined. – PrincessLilly Mar 3 '18 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.