Excel VBA - CountIF

[7wgh]

Hey,

I'm new to VBA and have hit a roadblock in what im trying to do. I have a column of data containing numbers from 10-360 in intervals of 10. All I need to do is calculate the percentage of each value in the column. For example, I need to count the number of entries of "10" and divide by the total number of entries. I did this using =COUNTIF(A2:A2200,"=10.0")/(2199) and so on but it was very time-intensive. I am looking for an easier way to use COUNTIF in VBA. If anyone can help get me started it would be appreciated. Thanks.

[nO_OnE]

Try this

you need to create a new sub and paste the following code:

'this is for the loop variable
    Dim i As Integer
    
    'this will be the target range
    Dim r As Range
    
    'this will be the target row
    Dim rw As String
    
    'this is the beginning constant portion of the formula
    Dim str1 As String
    
    'this is the ending portion of the formula that changes
    Dim str2 As String
    
    
    'this makes the target start at row 2
    'change this as necessary for the row that you want it to start on
    rw = 2
    
    'this is just the beginning portion of your formula
    str1 = "=Countif(A2:A30,"
    
 
    'this for loop sets i to start at the value of 10, changes by +10 (Step 10) for each iteration until it = 360
    For i = 10 To 360 Step 10
        'this sets the target cell to be "C" column at the row that the rw is set to
        Set r = Range("C" & rw)
        'this sets the variable equal to the first portion of your formula
        'the & i adds the value of i, which starts at 10 and goes up in increments of 10
        str2 = str1 & i & ")/(2199)"
        'this puts the formula in the target cell
        r.Formula = str2
        'this advances the target to the next row
        rw = rw + 1
    Next i

If you need more help, or you want to alter this to go across the columns instead of down rows, let me know and I'll update the code.

[7wgh]

hey thanks it worked great! The only other thing i am looking into doing is the denominator in the formula str2 =.../(2199) is actually equal to the total number of entries in column A2:A30(for example). Is it possible to change the denominator from 2199 to the count of non-blank data entries in a particular column? Thanks!

[nO_OnE]

You can, when you are talking about blank cells, do you mean that there may be random blank cells, or would it work if we determined the last cell with data in it?

[kaliman79912]

If I understand it right, it is just a matter of writing the right formula, no need for code. Lets say you have your numbers in column C, put the list on column D (10,20,30,40...360) put this formula in E4 and just copy it to the rest of the column.

=COUNTIF($C$4:$C$2200,"=" & D4)/COUNT($C$4:$C$2200)

The only flaw here is that it is only checking until row 2200, of course you can just put a huge number there in hopes that it is always covering your data. But I would prefer to create a named range and use it instead of the absolute references

=COUNTIF(myRange,"=" & D4)/COUNT(myRange)

[nO_OnE]

I was just looking that up, I forgot about counting the cells with data. I am used to just using the end function on Range.

So, Try:

    Dim i As Integer
    Dim r As Range
    Dim rw As String
    Dim str1 As String
    Dim str2 As String
    
    rw = 2
    str1 = "=Countif(A2:A30,"
    
    For i = 10 To 360 Step 10
        Set r = Range("C" & rw)
        str2 = str1 & i & ")/(Count(A2:A2200))"   'Replaced the original 2199 with the count function
        r.Formula = str2
        rw = rw + 1
    Next i

[7wgh]

Hi,

Thanks for the help. One last question regarding this issue. I now have one column of data that differentiates between > or < Wind speed by saying either "LS 15" or "GT 15". I need to use the CountIf macro above to perform 2 calculations, one for data with LS 15 and one for data with GT 15. I was wondering the best way to incorporate this into the macro, or if I need to do it another way. Thanks for the help.

[nO_OnE]

If those are the only two items you need to differentiate from then you can use a simple if statement. I am assuming that you need to utilize it on the formula above:

Dim i As Integer
    Dim r As Range
    Dim rw As String
    Dim str1 As String
    Dim str2 As String
    
    rw = 2
    str1 = "=Countif(A2:A30,"
    
    For i = 10 To 360 Step 10
        Set r = Range("C" & rw)
        If Left(r.Value, 2) = "LS" Then
            str2 = str1 & i & ")/(Count(A2:A2200))" 
            r.Formula = str2
        ElseIf Left(r.value, 2) = "GT" Then
            'your secondary formula
        End If
        rw = rw + 1
    Next i

Or you can use the select case method, which is better for differentiating between more items:

Dim i As Integer
    Dim r As Range
    Dim rw As String
    Dim str1 As String
    Dim str2 As String
    
    rw = 2
    str1 = "=Countif(A2:A30,"
    
    For i = 10 To 360 Step 10
        Set r = Range("C" & rw)
        Select Case Left(r.Value, 2)
            Case "LS"
                str2 = str1 & i & ")/(Count(A2:A2200))" 
                r.Formula = str2
            Case "GT"
                'Your code for GT
            End Select
        rw = rw + 1
    Next i