Finding # of Occurrences in various cells.

[The Exceller]

I have attached a spreadsheet which has various codes typed into a number of cells.

I am trying to find the number of occrrences for certain codes that appear in these cells. The problem is that the codes in the cells look as follows:

cell A13: 272.4;789.01;787.23;401.9;274.9;2725
cell A18: 530.3;787.20;041.86;578.1;455.6;565.0


That appears in one cell itself and the codes are separated by a semicolon.

What I need to do is find the number of occrrences that a certain code occurs. For example, how many times does 787.20 (from above) occur in the entire list from cell A5:A164?

It is difficult to siphon out certain numbers because they are all lumped together and separated by the semicolon.

I've attached the spreadsheet for ease of understanding.

I've tried to use the =countif function but it won't pick up everything it should because of the semicolon problem and the numbers being lumped together.

Any help is much appreciated!

[arthurbr]

Would attached help?
The formula are array formula, they should be entered with Ctrl+Shift+Enter

[The Exceller]

Nice Job arthurbr. I believe that does it. I didn't know it would need to be a CSE fomula like that. My only question is if it's really picking up all the numbers with the semicolon in place. It looks like it is but it would nice to hear your confirmation or thoughts on that.

Thanks again

[arthurbr]

The best way too see the formula at work is to try Tools - Formula auditing - Evaluate formula
The array formula returns an array of 0's and 1's depending on the fact that FIND returns a number or not, and sums the 0's and 1's
And it will return a number when it finds the string independently of the semi colon's presence

[Marcol]

This might help prove Arthurs' formulae

[arthurbr]

You can also avoid an array formula using

=SUMPRODUCT(--ISNUMBER(FIND(D2,$A$5:$A$164)))

[The Exceller]

Okay!, I'm going to go ahead and say it like this...

...you dudes are awesome! Nice work for sure! This is really helpful and again I've learned something new. Thanks a lot.