title: Alternative to CountIf Worksheet Function
body: <p>I am trying to use the <code>CountIf</code> <code>WorksheetFunction</code> to show how many values are above and below a given value.  The only catch is, I use a filter prior to that, and the ranges that are used are broken ranges.  I just learned that the <code>CountIf</code> function can only accept a solid range.  Is there an alternative to this, or perhaps a workaround?  My range is fairly inconsistent and has many, many breaks. </p>
  
  <pre><code> ElseIf CWYes = True Then
          Worksheets("Modified Item Extract").Range("$A$1:$CL$293662").AutoFilter Field:=1, Criteria1:="" &amp; PBH.Value
      Dim y As Double
      Dim z As Double
              PricePerKG = POCost
              Set ws = ActiveWorkbook.Worksheets("Modified Item Extract")
              Set relevant_array = ws.Range(ws.Range("B2"), ws.Range("B2").End(xlDown)).SpecialCells(xlCellTypeVisible)
  
              y = WorksheetFunction.PercentRank(relevant_array, POCost)
              Percentile = Format(y, "0.00%")
                  If y &gt; 0.7 Then Warning = "WARNING: your price is above the 70th percentile of items in the same PBH"
                  If y &lt; 0.3 Then Warning = "WARNING: your price is below the 30th percentile of items in the same PBH"
                  If y &gt; 0.3 And y &lt; 0.7 Then Warning = "Carry on: your price is between the 30th and 70th percentile of items in the same PBH"
                  If y = 0 Then MsgBox "The price you provided is out of the range of the PBH"
  
              Set ws = ActiveWorkbook.Worksheets("Modified Item Extract")
  
              Set relevant_range = ws.Range(ws.Range("B2"), ws.Range("B2").End(xlDown)).SpecialCells(xlCellTypeVisible)
              MsgBox relevant_range.Address
              z = WorksheetFunction.CountIf(relevant_range, "&lt;" &amp; POCost)
              PriceAbove = z
  </code></pre>