=SUMPRODUCT(--ISNUMBER(SEARCH("jim",A1:A6)),--(B1:B6="Phone"))
Let's break down in to pieces;
B1:B6="Phone" would create an array of TRUE and FALSE according to the
values in the range B1:B6. By coercing it by double negation (--),
we'll get an array of 0s and 1s.
SEARCH("jim",A1:A6) would create an array of the positions where it
found jim in the range A1:A6, if it didn't find it would return a VALUE
error, therefore, we check it by ISNUMBER function if the returned value
is a number or not, which would create an array of TRUE and FALSE and
again using double negation (--), we'll get and array of 0s and 1s.
SUMPRODUCT will just giving the summation of the product of these two
arrays.
Excel help should provide info on ISNUMBER function.
--
a7n9
------------------------------------------------------------------------
a7n9's Profile:
http://www.excelforum.com/member.php...o&userid=32149
View this thread:
http://www.excelforum.com/showthread...hreadid=562899