counting unique values

Karen Day · #1 (**permalink**) 11-07-11, 01:29

Hi I just cant seem to work this out. If I am not making myself clear enough please advise.

The following works if I just want to count the number of cells which contains a particular detail located on a seperate spreadsheet.

=COUNTIF('YWLP DATA 2011-2012'!$C$3:$C$2000,'YWLP Summary 2011-2012'!B7)

But the cells in c3:c2000 contain duplicate entries and I want it to count only unique values.

How can this be performed?

For example in the following data Tessa Hen and Nicole Daill are mentioned twice.The data is found on a sheet called 'YWLP DATA 2011-2012' and the field which I am referencing is on the 'YWLP Summary 2011-2012'. I want to look up the 'YWLP Summary 2011-2012' summary sheet cell B7 which for this example contains the words "steering group". I want to count the number of unique names which are allocated to "Steering Group". In this example 11 names are allocated to the steering group but only 9 names are unique.

Malisa Kli Group1
Samantha Kli Group1
Nicole Daill Steering Group
Tessa Hen Steering Group
Carla Tabot Steering Group
Danielle Big Steering Group
Emma Holiday Steering Group
Francesca House Steering Group
Don Kli Group1
Jess Chad Steering Group
Katie Berg Steering Group
Malisa Klicke Steering Group
Nicole Daill Steering Group
Tessa Hen Steering Group
Mel Thompson Group1

Thanks in advance for any help KD

Colin Legg · #2 (**permalink**) 11-07-11, 19:10

An array formula like this will do the job:

Code:

 
=SUM(IF(FREQUENCY(
    IF('YWLP DATA 2011-2012'!D3:D2000='YWLP Summary 2011-2012'!B7,
    IF('YWLP DATA 2011-2012'!C3:C2000<>"",MATCH("~"&'YWLP DATA 2011-2012'!C3:C2000,'YWLP DATA 2011-2012'!C3:C2000&"",0))),
    ROW('YWLP DATA 2011-2012'!C3:C2000)-ROW('YWLP DATA 2011-2012'!C3)+1),1))

It's an array formula so when you type it into the formula bar, you must complete the entry with CTRL+SHIFT+ENTER, not just the usual ENTER. If done correctly, Excel will automatically surround the formula with curly braces { }.

Alternatively you could write a little VBA UDF to do it (particularly handy if someone's already written it for you).

Karen Day · #3 (**permalink**) 11-08-11, 08:26

Hi Colin,
I want to thank you very much for the time you have taken to help me.

Unfortunately I cannot seem to get either the formula array or the VBA code to work.

I have attached the spreadsheet and put two comments in the spreadsheet.

I hope you can assist me again.

Thanks again.

KD

JerryDal · #4 (**permalink**) 11-08-11, 15:41

Here is VBA code that does the unique count you are looking for. The function checks for the specific category under Activity, and if the participant name is not already in an array that stores the participant names, the name is added to the array and the counter is incremented. Even if you don't use this, it was an interesting exercise.

Code:

Function Count_Unique(strCategory) As Integer
'returns a unique count of participant names for the specific category
Dim arrayTxt()
Dim strPartName As String, strCat As String
Dim ws As Worksheet
Dim counter As Integer
ReDim arrayTxt(1)

    Set ws = Worksheets("DATA 2011-2012")
    With ws
        i = 3
        arrayTxt(0) = ""
        Do While ws.Cells(i, 3) <> ""
            strCat = ws.Cells(i, 3)
            If strCat = strCategory Then
                strPartName = ws.Cells(i, 2)
                If Not isDuplicate(strPartName, arrayTxt) Then
                    arrayTxt(counter) = strPartName
                    counter = counter + 1
                    ReDim Preserve arrayTxt(counter + 1)
                End If
            End If
            i = i + 1
        Loop
    End With

    Set ws = Nothing
    
    Count_Unique = counter
      
End Function

Function isDuplicate(strName, ByRef arrayTxt()) As Boolean
'return True if value in strName is found in the array
Dim j As Integer, tmpBool As Boolean

    For j = 0 To UBound(arrayTxt) - 1
        If strName = arrayTxt(j) Then
            tmpBool = True
            Exit For
        End If
    Next
    
    isDuplicate = tmpBool
    
End Function

In cell B7:

Code:

=Count_Unique(B7)

Colin Legg · #5 (**permalink**) 11-08-11, 15:55

Hi Karen,

Based on the spreadsheet you attached, you have to change the sheet names and the column references in the formula. It becomes:

Code:

=SUM(IF(FREQUENCY(
    IF('DATA 2011-2012'!C3:C2000='Summary 2011-2012'!B7,
    IF('DATA 2011-2012'!B3:B2000<>"",MATCH("~"&'DATA 2011-2012'!B3:B2000,'DATA 2011-2012'!B3:B2000&"",0))),
    ROW('DATA 2011-2012'!B3:B2000)-ROW('DATA 2011-2012'!B3)+1),1))

which returns a result of 10. I have attached your worksheet with the formula in it for you. It is still an array formula.

Hi Jerry,

Nice work. I'd recommend that you pass the range which must contain "Steering Group" and the literal "Steering Group" into the function as arguments, otherwise you'll have problems with Excel's dependency tree.

JerryDal · #6 (**permalink**) 11-08-11, 17:54

Colin, I'm here to learn. Do you mean that instead of using "B7" in the cell
because of row inserts and deletes that might cause Excel to revise the
formula?

You say "pass the range...into the function...", could you expand on that a little?
Knowing that two specific columns are involved, why is it a better solution to
pass the range than to just refer to the two columns in code?

Thanks.
Jerry

Karen Day · #7 (**permalink**) 11-09-11, 07:02

Hi all,

Thank you so much for the assistance provided.
I really appreciate the assistance.

Colin, thanks so much it works perfectly, but you knew that already.

I wonder if you could help me a bit further on this spreadsheet?
It is once again attached.

My next problem is an extension of what Colin has already resolved for me.

So far Colin has helped me with counting the number of participants who are on the Steering Group EXCLUDING duplicates.

My next step is to count the number of participants who are on the Steering Group EXCLUDING duplicates and in age groups:
12 - 17
18 - 30
Over 30

My next step after that is to count the number of participants who are on the Steering Group EXCLUDING duplicates and by culture. But I think I will be able to do this if I know how to do the above.

Hoping someone can help
Karen Day

Karen Day · #8 (**permalink**) 11-11-11, 02:05

Hi everyone,

Regarding the 2nd request for assistance. I haven't had a reply.
Is this something that just can't be done and so no-one is answering?
Regards
Karen Day

Karen Day · #9 (**permalink**) 11-11-11, 23:14

For an answer to the above see "Counting Unique Values by Grouping" submitted by me.

Colin Legg · #10 (**permalink**) 11-13-11, 13:44

Hi Karen,

Sorry for the slow reply, I've been really busy.

You're using XL 2007 or later, so the multiple condition count formula in G20:G22 can be simplified from SUMPRODUCT to COUNTIFS. COUNTIFS is more efficient than SUMPRODUCT.

Code:

=COUNTIFS('DATA 2011-2012'!$C$3:$C$2000,"Steering Group",
    'DATA 2011-2012'!$D$3:$D$2000,B20)

Quote:

My next step is to count the number of participants who are on the Steering Group EXCLUDING duplicates and in age groups

You can extend the formula I gave you earlier to take into account this extra condition.

So the earlier formula was this:

Code:

=SUM(IF(FREQUENCY(
    IF('DATA 2011-2012'!$C$3:$C$2000='Summary 2011-2012'!$B$7,
    IF('DATA 2011-2012'!$B$3:$B$2000<>"",
    MATCH("~"&'DATA 2011-2012'!$B$3:$B$2000,'DATA 2011-2012'!$B$3:$B$2000&"",0))),
    ROW('DATA 2011-2012'!$B$3:$B$2000)-ROW('DATA 2011-2012'!$B$3)+1),1))

To extend it with the extra condition you do this:

Code:

=SUM(IF(FREQUENCY(
    IF('DATA 2011-2012'!$C$3:$C$2000='Summary 2011-2012'!$B$7,
    IF('DATA 2011-2012'!$B$3:$B$2000<>"",
   IF('DATA 2011-2012'!$D$3:$D$2000='Summary 2011-2012'!$B20,
    MATCH("~"&'DATA 2011-2012'!$B$3:$B$2000,'DATA 2011-2012'!$B$3:$B$2000&"",0)))),
    ROW('DATA 2011-2012'!$B$3:$B$2000)-ROW('DATA 2011-2012'!$B$3)+1),1))

Internet Services

Web Development

Digital Marketing

Consumer Tech