Hey---  This was meant for my post.  Sorry T.

Using your posted sample:

10...Complete 3-2-2007
10.................................
20...Complete 9-10-2006
20...Complete 1-1-2001

=SUMPRODUCT(--(A2:A5=10),--(ISNUMBER(SEARCH("complete",B2:B5))))

Returns 1

This is how it does that...

Each of these expressions will return an array of either TRUE or FALSE:

(A2:A5=10)
(ISNUMBER(SEARCH("complete",B2:B5)))

A2 = 10 = TRUE
A3 = 10 = TRUE
A4 = 10 = FALSE
A5 = 10 = FALSE

The SEARCH function "searches" the string for the substring "complete". This
search is case insensitive. If the string contains the substring the result
of SEARCH is the character number of the starting position of the substring.
For example:

Complete 3-2-2007
XX complete 1/1/2007
XX 1/1/2007

=SEARCH("Complete","Complete 3-2-2007") = 1 because the substring is found
and starts at character position 1.

=SEARCH("Complete","XX complete 1/1/2007") = 4 because the substring is
found and starts at character position 4.

=SEARCH("Complete","XX 1/1/2007") = #VALUE! because the substring is not
found.

We test the result of SEARCH to see if it is a number meaning the substring
was found by wrapping SEARCH inside of ISNUMBER:

(ISNUMBER(SEARCH("complete",B2))) = TRUE
(ISNUMBER(SEARCH("complete",B3))) = FALSE
(ISNUMBER(SEARCH("complete",B4))) = TRUE
(ISNUMBER(SEARCH("complete",B5))) = TRUE

SUMPRODUCT needs to work with numbers so we use the "--" to coerce the TRUE
and FALSE to 1 and 0 respectively.

--(A2 = 10) = 1
--(A3 = 10) = 1
--(A4 = 10) = 0
--(A5 = 10) = 0

--(ISNUMBER(SEARCH("complete",B2))) = 1
--(ISNUMBER(SEARCH("complete",B3))) = 0
--(ISNUMBER(SEARCH("complete",B4))) = 1
--(ISNUMBER(SEARCH("complete",B5))) = 1

Now we have 2 arrays of 1s and 0s. These 2 arrays are then multiplied
together:

=SUMPRODUCT({1;1;0;0},{1;0;1;1})

1 * 1 = 1
1 * 0 = 0
0 * 1 = 0
0 * 1 = 0

SUMPRODUCT sums the result of multiplying these arrays:

=SUMPRODUCT({1;0;0;0}) = 1

For more detailed info on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html