Count number of occurrences in array

[cortexnotion]

Hi there

I have a spreadsheet with 500k rows and 10 columns. Column 10 contains a set of co-ordinates. For each row in column 11 I need to count the number of occurrences the value in column 10 has appeared in the whole of Column 10.

In VBA I have put the sheet range into an array and used the following code line in to calculate the number of occurrences. Unfortunately it runs on a small data set but not the sheet of my size without hanging. Is there an alternative at all?

(Len(col10value) - Len(Replace(col10value, col10range, ""))) / Len(col10range)

Many thanks

 

[Z51]

Not sure if this is what you're after, but it will count all the instances of the value in I1 across all cells in columns A:J...

=SUM(COUNTIF(A:J,I1))