none
Counting occurance of a range of number RRS feed

  • Question

  • I have a excel data that needs to be analysed. I have a column of different numbers( with 15000 rows). I would like to count the number of times a value in the column is more than 8.9. I would need the program to record down the frequency whenever the value in the column is more than 8.9. Thank you.
    Wednesday, August 23, 2017 6:37 AM

All replies

  • Hello HaziqElvis19

    The simple way to approach this is to use the COUNTIF excel function.

    If you want a macro to achieve something similar, the following code assumes that you have all your data in column A and that row 1 is a header.  You can adapt it to your specific needs.

    Sub CountOver()

    Dim lngBottomRow As Long
    Dim lngCounter As Long
    Dim lngOverTarget As Long

        lngBottomRow = Cells(Rows.Count, 1).End(xlUp).Row
        For lngCounter = 2 To lngBottomRow
            If Range("A" & lngCounter).Value > 8.9 Then
                lngOverTarget = lngOverTarget + 1
            End If
        Next lngCounter
        MsgBox "The number of items greater than 8.9 is " & lngOverTarget & "."

    End Sub

    Wednesday, August 23, 2017 9:49 AM