Need help in counting

rainx

Board Regular
Joined
Jul 4, 2008
Messages
210
Sample Data: I have tis set of sample data that I have already managed to get the names counted in each class using vba code as shown below. Now I need to count those names that belongs to the singapore branch. I have done a match with my namelist using MATCH in column E, if return a number, it means a match wich means the person belongs to singapore brach. So now I need to count those names who belongs to SIngapore branch in each class. Please help thanks!!

Code:
Sub Count()
Dim oneCell As Range
With ThisWorkbook.Sheets("sheet1").Range("A:A")
    With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        .Offset(.Rows.Count, 0).Range("A1:A2") = [{"x";"---"}]
        With .Offset(0, 1)
            .Cells(1, 1).FormulaArray = _
                  "=IF((LEN(SUBSTITUTE(R[1]C1,""-"",""""))<(LEN(R[1]C1)-1)),SMALL(IF(LEN(RC1:R[99]C1)-LEN(SUBSTITUTE(RC1:R[99]C1,""-"",""""))>1,ROW(RC1:R[99]C1),9999),2) - SMALL(IF(LEN(RC1:R[99]C1)-LEN(SUBSTITUTE(RC1:R[99]C1,""-"",""""))>1,ROW(RC1:R[99]C1),9999),1)-3,"""")"
            .FillDown
            .Value = .Value
        End With
        .Offset(.Rows.Count, 0).Range("A1:A2").ClearContents
        On Error Resume Next
        For Each oneCell In .SpecialCells(xlCellTypeBlanks)
            If oneCell.Offset(-3, 1).Value Then oneCell.Offset(-3, 1).Value = 0
        Next oneCell
        With .Offset(0, 1).Cells(.Rows.Count, 1).End(xlUp)
            If .Value < 0 Then .Value = 0
        End With
        On Error GoTo 0
    End With
End With
End Sub

<table x:str="" style="border-collapse: collapse; width: 492pt;" width="655" border="0" cellpadding="0" cellspacing="0"><col style="width: 259pt;" width="345"> <col style="width: 69pt;" width="92"> <col style="width: 62pt;" width="83"> <col style="width: 49pt;" width="65"> <col style="width: 53pt;" width="70"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt; width: 259pt;" width="345" height="17">A</td> <td class="xl28" style="width: 69pt;" width="92">B</td> <td class="xl28" style="width: 62pt;" width="83">C</td> <td class="xl28" style="width: 49pt;" width="65">D</td> <td class="xl28" style="width: 53pt;" width="70">E</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 259pt;" width="345" height="17">Class 1</td> <td class="xl24" style="border-left: medium none; width: 69pt;" width="92">Instructor: </td> <td class="xl24" style="border-left: medium none; width: 62pt;" x:str="Tom " width="83">Tom </td> <td class="xl24" style="border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" x:str="02/11/2008 - 02/13/2008 8:30 AM - 5:00 PM " width="345" height="17">02/11/2008 - 02/13/2008 8:30 AM - 5:00 PM </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">Room:</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83">Singapore</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Calvin</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57725</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">tom</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">59085</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">mary</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">1486</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">david</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">1605</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">James</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">1658</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Phillip</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">2911</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Grace</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60567</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Kiro</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">CFN</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60202</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Adam</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58876</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Rain</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60042</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:err="#N/A" align="center">#N/A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Class 2</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">Instructor: </td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83">Ryan</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" x:str="04/14/2008 - 04/16/2008 8:30 AM - 5:00 PM " width="345" height="17">04/14/2008 - 04/16/2008 8:30 AM - 5:00 PM </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92">Room:</td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" x:str="Singapore " width="83">Singapore </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" width="65"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str=""> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Calvin</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">21385</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">31</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">tom</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57643</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">38</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">mary</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58949</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">55</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">david</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57547</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">58</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">James</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">57565</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">66</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Phillip</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58477</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">72</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Grace</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">58149</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">75</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Kiro</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60859</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">82</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Adam</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">20248</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">87</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Rain</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60902</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Rachel</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60169</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">108</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">Paulyn</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">59656</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">118</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">adeline</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60810</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">122</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">catherine</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">59249</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">126</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt; width: 259pt;" width="345" height="17">charles</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 69pt;" width="92"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 62pt;" width="83"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none; width: 49pt;" x:num="" width="65" align="right">60133</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" align="right">128</td> </tr> </tbody></table>
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
If column E return a number, it means it matches with my namelist, my namelist only contain those frm Singapore branch, actually I have no use for the position number it return frm the match formula, I simply use it just to identify if dey are frm Singapore. those that reutrn error or dun contain anything, it means they could not be found in the namelist.

Thanks!
 
Upvote 0
Then simply ?
Code:
Sub test()
Dim x As Long
With Columns("e")
    On Error Resume Next
    x = .SpecialCells(-4123, 1).Count
    MsgBox IIf(Err = 0, x, 0)
End With
End Sub
 
Upvote 0
But I want to count those that belongs to each class. which means like class 1 how many from singapore, class 2 how many, and so on and so for.. Yup...cos I have many many classes. In the data above, there are 3 classes.

Thanks!
 
Upvote 0
Your asmple data seems Class1 has nothing and Class2 has all.
Is this always happenening or you could have some numbers in Class1 sometime ?
 
Upvote 0
Hmm, there are cases where class 1 has those frm SIngapore too.. There are many many classes with different class names, so these are jus few classes I picked up randomly as sample data. Yup...

THanks!
 
Upvote 0
actually if you can help me change the code above that is counting the names in each class, I believe it will be able to apply to column E too, cos I tried changing but cant relli make it work...

Thanks alot!
 
Upvote 0
Sorry, my method will be different from your? code.
If you just want to edit your code, then you need to wait someone else to comes in.
Bye...
 
Upvote 0
I am open to other solutions too but thought I could give some suggestion.. sorry..

thanks anyway..
 
Upvote 0

Forum statistics

Threads
1,216,749
Messages
6,132,496
Members
449,730
Latest member
SeanHT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top