Counting Occurences

[Tomac]

Hey Everybody,

I am wondering if anybody can help me out with
this. I need to find a way to count occurences in a cell, and the
COUNTIF function will not work this is why: Every cell has more 200
6-digit numbers in it. For example cell A1 will have 200 6-digit
numbers. Like this every cell till A1000 has 6-digit numbers. Now i
have *ONE* 6-digit number in cells B1 to B1000. I would like to know if
there is a way to count the number of times a 6-digit number in B1 would
occur in a range from cell A1 to A1000. But here is the catch, all the A
cells have 200 6-digit numbers. I have been really breaking my head on
this. Please help.

 

[Ron Coderre]

Try this:

For misc text in A1:A1000 and test text in B1

C1:
=SUMPRODUCT(LEN(A1:A1000)-LEN(SUBSTITUTE(UPPER(A1:A1000),UPPER(B1),"")))/LEN(B1)

That formula counts the occurrences of the B1 value in the range
A1:A1000

Note: the UPPER function makes the formua NOT case sensitive.

Regards,
Ron

 

[Tomac]

Dear Coderre,

Thank you very much for the code. It helped a lot. I
have one more question to ask? How can i develop on the code, to see if
a singe value in B1 And a single value in C2 falls within the range
A1:A1000, following the same case in the previous posting where 200
6-digit numbers exist in a cell.

Thanks,
Thomas.