Function that counts how many numbers are in a range are between a MIN and MAX

[Dstars21]

I am trying to create a function that will count how many numbers are in a range on an excel sheet, and also between a min and a max number that are also on the excel sheet (Without using any excel formulas, Ex. countif). The range on the excel sheet also includes empty and non numeric texts. Fairly new to programming so i'm having a hard time setting it up

What i have so far is:

Function Countbetween(values As Variant)


Dim N As Variant
Dim totalbetween As Single
Dim counter As Integer

For Each N In values
If N.IsNumeric = True Then 'and great than minimum and less than maximum number
counter = counter + 1
End If
Next





End Function

 

[DanteAmor]

You can put an example of the data you have in a range and the expected result.

 

[Fluff]

How about

Function Dstars21(Ary As Variant, Optional Mn As Double, Optional Mx As Double) As Long
    Dim Itm As Variant
    
    For Each Itm In Ary
        If IsNumeric(Itm) And Not Itm = "" Then
            If Mn = Mx Then
                Dstars21 = Dstars21 + 1
            Else
                If Itm > Mn And Itm < Mx Then Dstars21 = Dstars21 + 1
            End If
        End If
    Next Itm
End Function

Used like either
=Dstars21(B1:E14)
or
=Dstars21(B1:E14,-5,37)

 

[DanteAmor]

Another way:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Range</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Min</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Max</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td style="text-align:right; ">5</td><td style="text-align:right; ">8</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >words</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >num21</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">5</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">6</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >num22</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">10</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">9</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">8</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">7</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Function</td></tr><tr><td >D2</td><td >=CountNumbers(A2:A12,B2,C2)</td></tr></table></td></tr></table>

Function CountNumbers(wRange As Range, wMin As Range, wMax As Range)
  Dim counter As Variant
  For Each counter In wRange
    If IsNumeric(counter) And counter >= wMin And counter <= wMax Then CountNumbers = CountNumbers + 1
  Next
End Function