CODEWITHSUNDEEP
excelexcel-formula
Deon
Deon

Reputation: 31

Filtering and then counting distinct values

This is for Excel: I've been tasked with counting distinct records after I have filtered the data. I have 330 rows with column A containing the 'name' and in Column B I have the name of a test that was done for each 'name', which each 'name' could have taken several iterations of the same test. The test results are in Column C. 

Col A -Student  Col B -Exam          Col C - Grade
Student 1       Exam 1              .80
Student 2       Exam 1              .50
Student 3       Exam 1              .90
Student 2       Exam 1              .75
Student 4       Exam 1              .90
Student 5       Exam 1              .55
Student 2       Exam 2              .90
Student 1       Exam 2              .90
....            ....                ...

If I filter col B for Exam 1, I want to count the unique number of students that have taken Exam 1.

Upvotes: 3

Views: 14411

Answers (4)

Dick Kusleika
Dick Kusleika

Reputation: 33165

Array entered

{=SUM(IF(FREQUENCY(IF(LEN(A1:A8)>0,MATCH(A1:A8,A1:A8,FALSE),""),IF(LEN(A1:A8)>0,MATCH(A1:A8,A1:A8,FALSE),""))*(B1:B9="Exam 1")>0,1))}

Note that since FREQUENCY returns one more data point than the source range, that the column B range is actually B1:B9 and this only works if B9 is not equal to Exam 1.

If you want to base the condition on what is filtered rather than on a certain column every time, I don't know of a way to do it with a formula. If you're not averse to VBA, you could use this simple UDF.

Public Function CountUniqueFiltered(rColumn As Range) As Long
    Dim rCell As Range
    Dim colUnique As Collection

    Set colUnique = New Collection

    For Each rCell In rColumn.Cells
        If Not rCell.EntireRow.Hidden Then
            On Error Resume Next
                colUnique.Add rCell.Value, CStr(rCell.Value)
            On Error GoTo 0
        End If
    Next rCell

    CountUniqueFiltered = colUnique.Count
End Function

Upvotes: 1

Grendler
Grendler

Reputation: 6837

Found this:

=SUMPRODUCT((A1:A30000<>"")/COUNTIF(A1:A30000,A1:A30000&""))

on the Excel Forum

Tested in on your example and .. it works :-)

Upvotes: 3

Fionnuala
Fionnuala

Reputation: 91376

In line with thursdaysgeek

strFile = Workbooks(1).FullName
''Note HDR=Yes, so column names can be used
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
Set rs = CreateObject("ADODB.Recordset")

strSQL = "SELECT DISTINCT Student, Exam FROM [Sheet4$] " _
       & "WHERE Exam='Exam 1'"

rs.Open strSQL, cn

For i = 0 To rs.Fields.Count - 1
    Sheets("Sheet5").Cells(1, i + 1) = rs.Fields(i).Name
Next

Sheets("Sheet5").Cells(2, 1).CopyFromRecordset rs

Upvotes: 0

thursdaysgeek
thursdaysgeek

Reputation: 7946

I'm not sure how column B is fitting in here, but...

select distinct column_A, count(*)
from table
where column_C = 'A+'
group by column_A

Upvotes: 0

Related Questions