0

I am trying to find out how many numbers in a column are above a certain number, so a column has 2 4 3 6 3, and I want to have a tally after the last row with how many are above 3, could this be done with an if conditional and how would it be done?
Thanks

2
Contributors
4
Replies
5
Views
10 Years
Discussion Span
Last Post by Dark_Omen
0

yeah I guess I could do it, are you talking about a VB macro, and if so how would you do that?
Thanks

0

Yes a VB Macro. Click Tools, Macro, Visual Basic Editor. Then click the Insert Menu, and Choose "Module". Then paste this into the module:

Public Sub ColumnCountAbove()
' /* ************************************************ */
' /* SubRoutine To Tally The Total Number Of Columns  */
' /* Which Have Values Greater Than A Specified Value */
' /* ************************************************ */

' /* Declare The Variables That We Plan To Use */
Dim ActiveColumn
Dim AllAbove
Dim TotalAbove
Dim I

' /* Get The Currently Active Column */
ActiveColumn = Excel.ActiveCell.Column

' /* Retrieve Information From The User, Regarding The Control Number */
' /* (That is, The Number From Which We Compare All Others To) */
AllAbove = InputBox("Above What Number?")

' /* If The User Enters Nothing, Just Quit */
If AllAbove = vbNullString Then Exit Sub

' /* Set Our Counter Variable To 1 */
' /* This Variable Keeps Track Of Which Row We Are In */
' /* And Which Row We Are Going To */
I = 1

' /* Since We Haven't Started Comparing Anything */
' /* The Total Tally Of How Many Are Above The Said */
' /* Numbers Should Be Zero... */
TotalAbove = 0

' /* Loop Until We Reach A Blank A Cell */
Do Until Cells(I, ActiveColumn) = vbNullString
    ' /* If The Value Of The Cell Is Greater Than The Value The User Gave */
    ' /* Then Add 1 To Our Tally */
    If Val(Cells(I, ActiveColumn).Value) > Val(AllAbove) Then TotalAbove = TotalAbove + 1
    
    ' /* Move To The Next Row Down */
    I = I + 1
Loop

' /* Stick The Tally Total In The First Empty Row, Same Column */
Cells(I, ActiveColumn).Value = TotalAbove

End Sub

Then should you want to, Click In The (Name) Portion in the properties box on the left, and give it a name (other than module1). I called mine: ColumnCntAbove. Then just above that, you'll see some folders and things. Right Click what should be the only module there (either with the name you gave it, or module1) and export it. Find a good place, where it will be easy to access, and export it there. This way, you can add the procedure any time you need it, by simply opening the visual basic editor, and going to the file menu, and import file.

I had written this as an excel addin (.xla file), but it doesn't work the way I wanted it to (When you went to run the macro, you had to actually type the name of the function, instead of it just being in the list). There is one flaw to this sub, and that is that if there is a blank row in the middle of the column somewhere, it will stop the tally at the first blank entry in the column. If this is a problem, AND the number of blank rows in each column that will be tallied is always the same (for example, row 5, in every column will always be blank) then we can bypass this problem.... otherwise, it seems like we are stuck with no blank lines.

Let me know how this function turns out and works for you. If you need, I can make a bunch of different subs that are like the one above (for tallying rows, or for greater than or equal to (as opposed to just greater than), etc, etc).

0

Thanks for the code, but I actually found a built in excel funtion that did what I needed. The countif statement. =COUNTIF(Range, "condition")

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.