Countif performance drain on big data set

[rpinxt]

I have a data set with over 120.000 lines.

In that set I want to use VBA to make a formula.
The formula multiplies 3 rows with eachtother. That outcome than gets divided by a fixed number.

So far so good formula runs fine even on that many lines.

However in that set that are some lines that get couted double in the formula.
Throwing out these lines is not an option as other columns of that line are needed.

So being smart I thought then I just let the macro count how often a certain number occurs in one of the columns.
Then I take my original formula and divide that by the outcome of the countif formula (most will be 1, and some are 2 or 3 and then the outcome of the formula is good again).

But damn what a huge difference with the original formula!
I could not even wait for it to end because 1% progress takes really minutes and my laptop is ready for take off! (and it is really not a slow laptop).

Is there a more efficient and better way then countif to identify how often a certain number in a column occurs?

ps: Manually I took out the column for the countif and ran it in a separate workbook/sheet.
That is faster but then I need to work with a vlookup again which maybe also is not desirable.

[rpinxt]

Ok maybe text only is a bit too abstract

I attached a sample book.
Only 4 columns and due to size limit of the file only 43.000 lines but still a big performance drain using the countif.
On the original file it will even be 3 times as slow...

Anybody who can speed the formula up?
(and ofcourse using VBA to copy it down efficient).

[karedog]

This macro will create the countif values on column G :

Please Login or Register  to view this content.

then you can change the formula on E2 to :

Please Login or Register  to view this content.

[rpinxt]

That is very impressive karedog!
The speed (on the example) is great.

Coding is way above my comprehension (doesn't look like a countif to me )but it does the trick.
Thank you very much!

[karedog]

You are welcome rpinxt, thanks for marking the thread as solved and rep.points.

Regards

[Prasand1]

Very Helpful, thanks Karedog.
Any thoughts on implementing it for Countifs.