excelvbaexcel-formulauniquenonblank

Count unique non-blank entries in a range

I have data like this in Excel:

Person1    A    A    B    A         C    3
Person2                                  0
Person3    A    B    C    D    E    F    6
Person4              A    A    A         1

I am trying to find a formula that replicates the number in the last cell of each row, the number of unique elements associated with that person, excluding blanks. So for example Person1 has 3 since there is A, B, and C even though there are three A's. The number of columns is fixed / the same for everyone. The values A, B, C, etc, are strings (as opposed to numerics).

Can this be done using a formula?

Solution

  • This should work for you:

    =SUMPRODUCT((B1:G1<>"")/COUNTIF(B1:G1,B1:G1&""))