Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- Tomac ------------------------------------------------------------------------ Tomac's Profile: http://www.excelforum.com/member.php...o&userid=31555 View this thread: http://www.excelforum.com/showthread...hreadid=512494 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Occurences
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 -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=512494 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Occurences
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. -- Tomac ------------------------------------------------------------------------ Tomac's Profile: http://www.excelforum.com/member.php...o&userid=31555 View this thread: http://www.excelforum.com/showthread...hreadid=512494 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Occurences Between Dates | Excel Worksheet Functions | |||
Counting occurences of a name | Excel Worksheet Functions | |||
Counting the occurences | Excel Worksheet Functions | |||
Counting Occurences | Excel Discussion (Misc queries) | |||
Help on counting occurences | Excel Programming |