CountiF Function is not counting the accurate number of repeated values

  • 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

  • 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.

  • 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.

  • 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:


    One way to do this in VBA is something like:

    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:

    For i = 2 to 100
  • COUNTIF will also return unexpected results if there are leading or trailing spaces in any cell.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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.

  • 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

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

    and the corresponding code at the end of the macro:

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!