Counting Occurences

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.

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