'excel/vba: count number of unique male and female students in list

I have a database of students with names and their gender, however the list contains repeats of students on different dates. How do I count the number of unique male students and unique female students on my list?

Here is a sample database:

Name Date Gender
A   8/1/2013    M
B   8/2/2013    F
C   8/2/2013    F
A   9/2/2013    M
A   9/3/2013    M
C   8/31/2013   F
B   8/15/2013   F
D   10/5/2013   M

The total count for unique males should be 2, and unique females should be 2.

I tried to play around with the sum(if(frequency)) variation formula but without luck. I'm not sure how to tie it to using the names.

I don't mind using VBA code either.

Any suggestions would be appreciated.

Thanks!

listvbaexcel


Solution 1:[1]

Assuming that each name will always have the same gender if repeated (!) you can use a formula like this to count different males in the list:

=SUMPRODUCT((C2:C100="M")/COUNTIF(A2:A100,A2:A100&""))

obviously change M to F for female count

Solution 2:[2]

Sort the table by name A to Z first and then run this macro. Note that I am hard coding the range A2:A9.

This will only work if you sort your list first. Select all columns and rows and right click and sort A to Z by the name column.

Sub LoopRange()
  Dim rCell As Range
  Dim rRng As Range
  Set rRng = Sheet1.Range("A2:A9")
  Dim countM As Integer
  Dim countF As Integer
  Dim name As String

  For Each rCell In rRng.Cells
    If rCell.Value > name Then
        If rCell.Offset(0, 2).Value = "M" Then
            countM = countM + 1
        End If
        If rCell.Offset(0, 2).Value = "F" Then
            countF = countF + 1
        End If
    End If
    name = rCell.Value

  Next rCell

  MsgBox ("Males = " & countM & ", Females = " & countF)
  'Range("E3").Value = countF
  'Range("E4").Value = countM
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 barry houdini
Solution 2 Automate This

Related Questions