Count unique non-blank entries in a range

Question

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?

score 7 · Accepted Answer · answered Jul 25 '18 at 13:10

7

This should work for you:

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

answered Jul 25 '18 at 13:10

tigeravatar

26,199
5
30
38

3

@user51819 The numerator `(B1:G1<>"")` takes each cell and checks if it's blank, if it is blank it fails the check and gets counted as a 0, otherwise 1. Then the denominator gets the total count of each element in the range. The division then applies 1/Count for each element, such that if you have 3 of one element it becomes 1/3+1/3+1/3. For example, on sample line 1, the breakdown is: 1/3+1/3+1/1+1/3+0/1+1/1 = 0.333+0.333+1+0.333+0+1 = 3 – tigeravatar Jul 25 '18 at 13:42
Really cool. Is there any risk of precision issues with this approach since it's working with fractions? – user51819 Jul 25 '18 at 13:45
1

@user51819 There can be, but you can wrap the formula in `ROUND` if you run into any issues with that. – tigeravatar Jul 25 '18 at 13:46
2

I prefer doing these with FREQUENCIES for that reason but COUNTIF does work surprisingly well :-) – Tom Sharpe Jul 25 '18 at 14:02
2

FRQUENCY I mean – Tom Sharpe Jul 25 '18 at 14:08
1

Plz see https://exceljet.net/formula/count-unique-text-values-in-a-range – Tom Sharpe Jul 25 '18 at 14:47

Vityata · Answer 2 · 2018-07-25T13:57:03.787

4

This is what I found:

=SUMPRODUCT(1/COUNTIF(B1:F1,B1:F1&""))

It works quite interestingly:

It sums the results of the countifs, and it divides each one by 1.Thus, if you have the above example, it returns 4, because it sums:

1/1 + 1/3 + 1/3 + 1/1 + 1/3 + 1/3

edited Jul 25 '18 at 13:57

answered Jul 25 '18 at 13:09

Vityata

42,633
8
55
100

I tested this and I don't think this gives the correct results on example row 3 and 4 – tigeravatar Jul 25 '18 at 13:12
@tigeravatar - yup, my test used to be a bit fake... I had 3 times `A` and I was expecting 3, thus I considered it working. – Vityata Jul 25 '18 at 13:14
Hmm... now it gives a #DIV/0! error if there are any blank cells in the row – tigeravatar Jul 25 '18 at 13:16
@tigeravatar - slowly getting there :) I knew there was something with `1/`. – Vityata Jul 25 '18 at 13:25

QHarr · Answer 3 · 2018-07-25T13:32:59.250

3

An UDF to use in sheet

Public Function GetUniqueCount(ByVal rng As Range) As Long
    Dim dict As Object, currCell As Range
    Set dict = CreateObject("Scripting.Dictionary")
    For Each currCell In rng
        If Not IsEmpty(currCell) Then dict(currCell.Value) = 1
    Next currCell
    GetUniqueCount = dict.Count
End Function

In sheet use:

edited Jul 25 '18 at 13:32

answered Jul 25 '18 at 13:13

QHarr

83,427
12
54
101

Count unique non-blank entries in a range

3 Answers3