Alternative to CountIf Worksheet Function

Question

I am trying to use the CountIf WorksheetFunction 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 CountIf 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.

 ElseIf CWYes = True Then
        Worksheets("Modified Item Extract").Range("$A$1:$CL$293662").AutoFilter Field:=1, Criteria1:="" & 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 > 0.7 Then Warning = "WARNING: your price is above the 70th percentile of items in the same PBH"
                If y < 0.3 Then Warning = "WARNING: your price is below the 30th percentile of items in the same PBH"
                If y > 0.3 And y < 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, "<" & POCost)
            PriceAbove = z

Are you already using VBA? If you're using CountIf in VBA -- can you show the code where you use it? — Nicholas Patton, Jun 24 '16 at 17:49

Scott Holtzman · Answer 1 · 2016-06-24T18:01:58.397

0

One option is to loop through the cells:

For each relevant_cell in relevant_range
   If relevant_cell < POCost Then PriceAbove = PriceAbove + 1
Next

Another option is to just filter the data set again:

  Set ws = ActiveWorkbook.Worksheets("Modified Item Extract")

  With ws

      With .Range("$A$1:$CL$293662")
          .AutoFilter Field:=1, Criteria1:="" & PBH.Value
          .AutoFilter Field:=2, Criteria1:="<" & POCost
      End With

      Dim PriceAbove As Double
      Price Above = .Range(.Range("B2"),.Range("B2").End(xlDown)).SpecialCells(xlCellTypeVisible).Rows.Count

  End With

edited Jun 24 '16 at 18:01

answered Jun 24 '16 at 17:56

Scott Holtzman

27,099
5
37
72

Then would I use a WorksheetFunction.Count on the full range of POCost to count how many are in that column? – cam Jun 24 '16 at 18:45
@cam - I am confused? where does the `full range of POCost` come into play? It's no where in your code above. `POCost` is only reference as a criteria to perform against a `COUNTIF` – Scott Holtzman Jun 24 '16 at 19:10
I have now included the full code up above. I tried to hold some back as to not confuse people. However, POCost is pretty much what I am evaluating and it is what is in column B (what I am filtering), column A is PBH (which is an identifying number of different categories of the POCosts and what I am filtering first). – cam Jun 24 '16 at 19:27
And 'PriceAbove' is an output box trying to show how many prices are above the POCost we are using – cam Jun 24 '16 at 19:30
@cam - then the code I pro vided should work exactly the way you want. have you tested it? It filters `A` for `PBH` then `B` for anything over `POCost`. The result is the ocunt of rows that match the criteria. – Scott Holtzman Jun 24 '16 at 20:20

Alternative to CountIf Worksheet Function

1 Answers1