# Thread: Counting Unique Values by Grouping

1. Registered User
Join Date
May 2002
Posts
157

## Unanswered: Counting Unique Values by Grouping

Hi there
Thanks to Colin Legg I have a formula
=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))

This works to provide the following information:
It is trying to count the number of unique names (does not count duplicates) which are on the “Steering Group”.
It looks at my spreadsheet called [Summary 2011-2012] cell B7 (which gives the value “Steering Group”)
Then it looks at my spreadsheet called [Data 2011-2012] specifically at cells c3:c2000 to find any records with the value “Steering Group” whilst at the same time ignoring any duplicated names in located in cells B3:B2000.
The formula works beautifully (even though I don’t understand how).

I now need to know the number of unique names (does not count duplicates) which are on the “Steering Group” (same as the formula above) but which are identified as being in the following age groups:
12 - 17
18 - 30
Over 30

I have attached the workbook.
Can anyone help me with this additional step?
PS if you believe this cannot be done, then please advice as I will have to rethink the whole way of keeping the data.

2. Registered User
Join Date
May 2002
Posts
157
Hi everyone,

I have achieved the following:
"I now need to know the number of unique names (does not count duplicates) which are on the “Steering Group” (same as the formula above) but which are identified as being in the following age groups:
12 - 17
18 - 30
Over 30"

I have achieved what I wanted to do. Not sure if it is as neat as it should be but it does the job. Please see Speadsheet attached.

Basically I added an extra column into the data sheet. I used this column as an exception column to allocate any "Steering Group" data by age group.

Back on the Summary Data Sheet I created the following formula (most of which was kindly provided by Colin Legg):

=(COUNTIF('DATA 2011-2012'!\$D\$3:\$D\$2000,B21))
-(SUMPRODUCT(('DATA 2011-2012'!\$C\$3:\$C\$2000="Steering Group")*('DATA 2011-2012'!\$D\$3:\$D\$2000=B21)))
+SUM(IF(FREQUENCY(
IF('DATA 2011-2012'!\$E\$3:\$E\$2000='Summary 2011-2012'!B21,
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))

This might be 'dirty' but it does the job.

If you can think of a better way of doing it, then please let me know.

Regards

Karen Day

3. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
Not sure about your counts. For Steering Group 18 - 30, I count 10. If I am
correct then I have an alternate solution.
You have added a formula to your data sheet in column E. I replaced your formula in column E with this:
Code:
=C3 &" "& D3
and these formulas in your summary sheet C22 and C23:
Code:
=CountNames("Steering Group 18 - 30")
=CountNames("Networking Session Over 30")
And with this code in a module that searches through the names and
returns a count of unique names for the parameter that you send to
the function.
Code:
Function CountNames(dataKey As String) As Integer
Dim ws As Worksheet
Dim k As Long, aCounter As Long
Dim arrayText() As String, pName As String
ReDim arrayText(1)

Set ws = Worksheets("DATA 2011-2012")

With ws
k = 3
Do While .Cells(k, 2).Value <> ""
If .Cells(k, 5).Value = dataKey Then
pName = .Cells(k, 2).Value
If Not isDuplicate(pName, arrayText) Then
arrayText(aCounter) = pName
aCounter = aCounter + 1
ReDim Preserve arrayText(aCounter + 1)
End If
End If
k = k + 1
Loop
End With

Set ws = Nothing

CountNames = aCounter

End Function

Function isDuplicate(txtValue, ByRef arrayText) As Boolean
Dim i As Integer
Dim tmpBool As Boolean

For i = 0 To UBound(arrayText) - 1
If arrayText(i) = txtValue Then
tmpBool = True
Exit For
End If
Next

isDuplicate = tmpBool

End Function
I hope this helps.

4. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
Oops, the counts I looked at were Age section starting on row #20. Your count of 10 on row 15 is correct. So the question still is "can your formula solution be done in any simpler way?"
Jerry

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•