Counting occurences which includes text

[Tomac]

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.

 

[Tom Ogilvy]

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

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