CountiF Function is not counting the accurate number of repeated values

mythu · Feb 4th 2022

I am using CountIF VBA version which gives me ease due to its fast calculation while working with the large datasets. My Data has text values as well as numerice values so it is basicaly a combination of different values.

I just want to count the number of repeated values but my function is giving wrong result as you can see in attached file.

I would appreciate if someoene can look into it and make it more efficent in terms of:

Accurate count for repeated values

Code Speed

and the last thing is i want to use this code in Personal.XLSB so i can recall it using shortkey.

I hope to get help. Thanks

**royUK** · Feb 5th 2022

I don't understand your logic. COUNTIF would be far faster than your Function, which uses a Loop. Inbuilt Excel Functions will always be more efficient than Excel VBA. COUNTIF returns the correct answer.

mythu · Feb 5th 2022

Yes you're right COUNTIF is more faster than VBA but that time COUNTIF is not giving an accurate answer. You can try on attached file.

First i used COUNTIF then SUMPRODUCT since countif answers were wrong then went to sumproduct which was perfect but its processing were slow over large datasets.

Then found above code. I would appreciate if you could help to modify the above code.

mythu · Feb 7th 2022

The problem is simple COUNTIF answers are wrong with the above attached data, which forced me to move with the code.

gijsmo · Feb 7th 2022

I suspect COUNTIF is not providing the correct answers because you have a mixture of numbers and numbers stored as text.

You should be able to work around this by forcing a formatted count. As an excel formula, this would look something like:

Code

 =COUNTIF($A$2:$A$349397,TEXT($A12,"#"))

One way to do this in VBA is something like:

Code

Sub AltCountIf()
  Dim x, y
  Dim i As Long
 
  With Application
    .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
  End With
  
  With Sheet1.Cells(1).CurrentRegion
    'assume column A contains the data values to be counted
    x = .Columns(1).Value
    'assume column B will hold the results
    y = .Columns(2).Value 'don't really need values, will just initialise y
    
    'skip the header row and loop through to do the countifs
    For i = 2 To UBound(x, 1)
      y(i, 1) = Application.CountIf(.Columns(1), Format(x(i, 1), "#"))
    Next
    
    'write the results back to column B
    .Columns(2).Value = y
  End With

  With Application
    .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
  End With
  
  MsgBox "Completed"  
End Sub

Display More

This loads the entire dataset to be counted into an array and then places the countif values into a second array.

It may be more efficient than other types of loops however with nearly 350,000 rows this is still going to be very slow.

Also, loading all the data into arrays first may be more efficient but it will be memory intensive.

If you want to test it first maybe try using a smaller loop eg:

Code

For i = 2 to 100

**KjBox** · Feb 7th 2022

COUNTIF will also return unexpected results if there are leading or trailing spaces in any cell.

mythu · Feb 8th 2022

gijsmo I like the way you wrote this code and formula but both are extremly slow even then mine above code. I have more than 350K rows wit the a lot data. Where every function goes slow.

I appreciate your help.

KjBox Is there any solution that you can provide or modify the above mine code to calculate accurate result. I do not want to remove the 0 from start of the string i need to keep these zero.

gijsmo · Feb 8th 2022

The way the code is currently written, it is only looking forward at any point in time ie, it is not looking at the whole set of data to determine the count for each row of data. This is why any countif code takes so long to run.

Your code currently provides accurate results if the rows of matching data are sequential eg rows 1935 and 1936.

This code would probably work as you require it if the data is sorted first, assuming data sorting does not interfere with anything else you may be doing.

You will also speed up processing by adding the following at the start of the macro

Code

  With Application
    .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
  End With

and the corresponding code at the end of the macro:

Code

  With Application
    .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
  End With

mythu · Feb 8th 2022

Thank you very much for the assistance gijsmo

gijsmo · Feb 9th 2022

You are welcome. Hopefully this will move you forward.

CountiF Function is not counting the accurate number of repeated values

Participate now!

Share

Tags