Counting number matches in ranges with string text

[daverjaye]

Hello all,


I am trying to figure out a formula that will help me count how many times a particular number sequence is appearing in a range of string text that could be anywhere from 4 characters on up. The text could appear as:

A B C

1.02 1.02 1.03

1.03 1.03 1.05

1.05 1.05 1.02

1.02 1.03 1.02 1.02 1.03

1.02, 1.03 1.02 1.02, 1.03, 1.05


Some text will have a comma, semicolon or spaces after it and could have a combination of both. I have been able to create a formula that will count the occurence based on individual cells. So if I wanted to count when 1.02(Cell D8) has occured in cell A5 the formula would be =COUNT(FIND(D8,A5,LEN(1))). If the formula is entered as =COUNT(FIND(D8,A1:A5,LEN(1))), Excel tells me that the formula result is 3 in the Function Arguments window but when entered the result is 0. Most likely due to the fact that there are #VALUE! errors in the range string.


Any help would be greatly appreciated.

 

[Hui]

Is

1.02 1.02. 1.03

In 1 cell or 3?

 

[daverjaye]

I noticed that the formatting didn't carry over. Sorry about that. Here is the text entered for each cell:


A1 = 1.02

A2 = 1.03

A3 = 1.05

A4 = 1.02 1.03

A5 = 1.02, 1.03


B1 = 1.02

B2 = 1.03

B3 = 1.05

B4 = 1.02

B5 = 1.02


C1 = 1.03

C2 = 1.05

C3 = 1.02

C4 = 1.02 1.03

C5 = 1.02, 1.03; 1.05

 

[Meedan]

Use below Array Formula -


=SUM(IF(ISNUMBER(FIND(D8,A1:A15)),1,0))


Remember, enter this as an array formula (press ctrl-shift-enter rather than just pressing enter.)


I have also worked out a simple formula, but its not worth.

=COUNT(FIND(D8,A1,1),FIND(D8,A2,1),FIND(D8,A3,1),FIND(D8,A4,1),FIND(D8,A5,1))

 

[daverjaye]

Thanks Meedan.


The formula works perfectly. I also tried entering a shortened version of my original formula =COUNT(FIND(D8,A1:C5)) as an array formula and it works as well. Looks like it all came down to just entering the formula as an array.


Appreciate the help.

 

[Meedan]

Oops.. you are correct. I should have tried your formula as array.


Thanks for letting us know.