COUNTIF returns either 0 or number of cells

Question

When using COUNTIF function in making my VB sub I faced a problem: COUNTIF returns either zero or number of cells in a range. Moreover, sometimes it returns correct numbers when I use another data.

Here is the code I use:

Sub CountifPerc()

Dim i As Integer
Dim MyArr() As Double

Set InitialRange = Range("A1:A250")
InitialRangeSize = InitialRange.Cells.Count
ReDim MyArr(InitialRangeSize - 1) As Double

For i = 1 To InitialRangeSize
  MyArr(i - 1) = Application.WorksheetFunction.CountIf(InitialRange, "<=" & InitialRange(i).Value) / InitialRangeSize
Next i

End Sub

Could you, please, help me with this question?

UPD: I've found out that the problem occurs when I use non-integer data. Integer data works well.

arcadeprecinct · Accepted Answer · 2016-05-15T00:32:23.177

I think your problem is your system locale. The following is based on the behavior of my machine with locale Germany but system language English as well as decimal separator set to . in the excel settings.

"<=" & InitialRange(i).Value

This produces a string that depends on your settings. Apparently the system locale is used when InitialRange(i).Value is converted to a string. That means that if your systems decimal separator is a comma then "<=" & 1/2 will produce "<=0,5" even if you set your excel settings to something else.

However, what you want in your case is "<=0.5" because apparently Application.WorksheetFunction.CountIf requires . as decimal separator (even though COUNTIF when entered on the worksheet requires the separator from the excel settings!)

I can think of two workarounds:

"<=" & Replace(InitialRange(i).Value,",",".") 'just hope that your system doesn't insert thousands separators

or

"<=" & str(InitialRange(i).Value) 'str uses . as decimal separator

score 0 · Answer 2 · answered May 14 '16 at 23:45

By using random data, and iterating pieces of the If..Then loop, I was able to see what you were doing, but I didn't have any issues with the code. Initial values of both integers and doubles gave equally valid responses. Here is the how I ran the code:

Sub CountifPerc()

  Dim i As Integer
  Dim MyArr() As Double

  Set InitialRange = Range("A1:A250")
  InitialRangeSize = InitialRange.Cells.Count

  ReDim MyArr(InitialRangeSize - 1) As Double

  For i = 1 To InitialRangeSize

  MyArr(i - 1) = Application.WorksheetFunction.CountIf(InitialRange, "<=" & InitialRange(i).Value) / InitialRangeSize

    Range("B" & i).Value = i
    Range("C" & i).Value = InitialRange(i).Value
    Range("D" & i).Value = Application.WorksheetFunction.CountIf(InitialRange, "<=" & InitialRange(i).Value)
    Range("E" & i).Value = MyArr(i - 1)

  Next i
End Sub

Here is an image of the output with non-integer random numbers, using =RAND()*1000 in column A: Excel Worksheet output. What else have you tried?

Thank you! However, I do not want to use any Excell cells in solving this problem! I actually used another code to solve this problem until arcadeprecinct helped above: For i = 1 To InitialRangeSize d = 0 For c = 1 To InitialRangeSize If InitialRange(i) >= InitialRange(c) Then d = d + 1 End If Next c d = Application.WorksheetFunction.CountIfs(InitialRange, "<=" & InitialRange(i).Value) MyArr(i - 1) = d / InitialRangeSize Next i — Alex, May 15 '16 at 12:35

COUNTIF returns either 0 or number of cells

2 Answers2