Counting occurences which includes text
 

Hey All,

I was trying to find a way to count occurences for multipl
criteria. Here is the situation: I have 200 6-digit numbers in eac
cell with a range from A1:A200, i.e A1 has 200 6-digit numbers and s
does A2, A3...A200. I want to find the number of times a Value in B
occurs in A1:A200. But the value in B1 should correspond to a Text i
C1, and count the number of times the text occurs from a range o
C1:C200. Let me give you an example. A1=(234789 265789 98756
436278.......) all the way to A200, which has 200 6 digit numbers. Now
the value in B1=436278 and the Value associated with B1 is C1=Texas.
would like to find an efficient way of counting the number of times
value in B1=C1 occurs in A1:A200. I know this is a big question, bu
please help. Thanks

--
Toma
-----------------------------------------------------------------------
Tomac's Profile: http://www.excelforum.com/member.php...fo&userid=3155
View this thread: http://www.excelforum.com/showthread.php?threadid=51320

Counting occurences which includes text
 

I don't see where the value in column C has much bearing on the problem.

How about if B1=436278 and C1 = Bozo

would that change the results?

anyway, try

=countif(A1:A200,"*" & b1 & "*")

if any value in C is good and only an empty cell makes a difference

=if(C1="","",countif($A$1:$A$200,"*" & b1 & "*"))

then drag fill down the column.

--
Regards,
Tom Ogilvy



"Tomac" wrote in
message ...

Hey All,

I was trying to find a way to count occurences for multiple
criteria. Here is the situation: I have 200 6-digit numbers in each
cell with a range from A1:A200, i.e A1 has 200 6-digit numbers and so
does A2, A3...A200. I want to find the number of times a Value in B1
occurs in A1:A200. But the value in B1 should correspond to a Text in
C1, and count the number of times the text occurs from a range of
C1:C200. Let me give you an example. A1=(234789 265789 987563
436278.......) all the way to A200, which has 200 6 digit numbers. Now,
the value in B1=436278 and the Value associated with B1 is C1=Texas. I
would like to find an efficient way of counting the number of times a
value in B1=C1 occurs in A1:A200. I know this is a big question, but
please help. Thanks.


--
Tomac
------------------------------------------------------------------------
Tomac's Profile:
http://www.excelforum.com/member.php...o&userid=31555
View this thread: http://www.excelforum.com/showthread...hreadid=513206

Counting occurences which includes text
 

I don't get how B1 can = C1 if B1 = a number and C1 is text. Does line A2
use B2 and C2, or B1 and C2, or B1 and C1:C200?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Tomac" wrote in
message ...

Hey All,

I was trying to find a way to count occurences for multiple
criteria. Here is the situation: I have 200 6-digit numbers in each
cell with a range from A1:A200, i.e A1 has 200 6-digit numbers and so
does A2, A3...A200. I want to find the number of times a Value in B1
occurs in A1:A200. But the value in B1 should correspond to a Text in
C1, and count the number of times the text occurs from a range of
C1:C200. Let me give you an example. A1=(234789 265789 987563
436278.......) all the way to A200, which has 200 6 digit numbers. Now,
the value in B1=436278 and the Value associated with B1 is C1=Texas. I
would like to find an efficient way of counting the number of times a
value in B1=C1 occurs in A1:A200. I know this is a big question, but
please help. Thanks.


--
Tomac
------------------------------------------------------------------------
Tomac's Profile:
http://www.excelforum.com/member.php...o&userid=31555
View this thread: http://www.excelforum.com/showthread...hreadid=513206