the string "Bear" could occur anywhere in the text

Using this sample data:

2....not here...Goat
3....bear here...Goat
4.....here bear...Goat
5.....................Goat
6.....junk.................
7.....skunk.......Goat
8.....monk...............
9.....big bear....Goat
10...bear with me...Goat

And this formula:

=SUMPRODUCT(--(ISERROR(SEARCH("Bear",B2:B2000))),--(L2:L2000="Goat"))

The result = 3

Rows 2, 5 and 7 are being counted.

--
Biff
Microsoft Excel MVP


"Big UT Fan" wrote in message
...
Biff,
Here is what I'm using but both your suggestion and Eduardo's are
counting all instances of "Goat" rather than only the instances where
"Goat"
is in col L and the corresponding value in col B does not contain "Bear".
Fyi, the string "Bear" could occur anywhere in the text in col B and be
preceded or followed by any text.

=SUMPRODUCT(--('020810'!B2:B2000<"Bear"),--('020810'!$L$2:$L$2000="fvt/cvt"))

Additionally, once I get this working I would like to add additional
conditionals such as counting the instances where col L equals "Goat" AND
the
value in col A equals "Rabbit", "Squirrel" or "Gopher" AND the value in
col B
does not contain "Bear".

Thanks, Mike

"T. Valko" wrote:

Try this... works in any version of Excel.

=SUMPRODUCT(--(B1:B10<"Bear"),--(L1:L10="Goat"))

This version will only work in Excel 2007 or later.

=COUNTIFS(B1:B10,"<Bear",L1:L10,"Goat")

Better to use cells to hold the criteria.

A1 = Bear
A2 = Goat

=SUMPRODUCT(--(B1:B10<A1),--(L1:L10=A2))

=COUNTIFS(B1:B10,"<"&A1,L1:L10,A2)

--
Biff
Microsoft Excel MVP


"Big UT Fan" wrote in message
...
I need to count the cells in a worksheet where col B doesn't contain a
certain value and col L contains a different value. So...what I need
to
do
is count the rows where col B doesn't contain "Bear" and col L contains
"Goat". Your help is appreciated.


.