Counting occurences which includes text
 

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
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 i
A1:A200. But the value in B1 should correspond to a Text in C1, an
count the number of times the text occurs from a range of C1:C200. Le
me give you an example. A1=(234789 265789 987563 436278.......) all th
way to A200, which has 200 6 digit numbers. Now, the value in B1=43627
and the Value associated with B1 is Texas in C1:C200 range. I woul
like to find an efficient way of counting the number of times a valu
in B1 occurs in A1:A200 which is also counting at the same time Texa
occurs in C1:C200 range. *Thus i would like to find how many time
B1=436278 occurs in A1:A200 and at the same time it should only regar
the TEXAS values in a C1:C200 range.

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

Counting occurences which includes text
 

=SUMPRODUCT(--(ISNUMBER(FIND(B1,$A$1:$A$200,1))),--($C$1:$C$200=C1))

in the first row, then drag fill down the column.

--
Regards,
Tom Ogilvy


"Tomac" wrote in
message ...

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 Texas in C1:C200 range. I would
like to find an efficient way of counting the number of times a value
in B1 occurs in A1:A200 which is also counting at the same time Texas
occurs in C1:C200 range. *Thus i would like to find how many times
B1=436278 occurs in A1:A200 and at the same time it should only regard
the TEXAS values in a C1:C200 range.*


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