Counting a row based on criteria in 2 cols

[Big UT Fan]

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.

 

[Eduardo]

Hi,

=sumproduct(($B$1:$B$1000<>"Bear")*($L$1:$L$1000="Goat"))

if this helps please click yes thanks

 

[T. Valko]

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)

 

[Big UT Fan]

This doesn't seem to be working...here's what I'm using. Again, I only want
to count the rows where col L CONTAINS "Goat" AND col B DOES NOT CONTAIN
"Boo".

=SUMPRODUCT(('020810'!$B$2:$B$2000<>"*Boo*")*('020810'!$L$2:$L$2000="*Goat*"))

 

[T. Valko]

CONTAINS "Goat" AND col B DOES

NOT CONTAIN "Boo".

What happened to "Bear" ? <g>

Looking at your formula:

=SUMPRODUCT(('020810'!$B$2:$B$2000<>"*Boo*")*('020810'!$L$2:$L$2000="*Goat*"))

Are you trying to use wildcards? If so, wildcards won't work with
SUMPRODUCT.

So, does that mean the cell might contain more text than just Goat or Boo?
Like:

What a Goat
Boo who

 

[Eduardo]

Hi,
why do you use wildcards is because Boo is contained in a text, could you
post what is in column B thanks

 

[Eduardo]

Hi
use this

=COUNTIFS('020810'!$B$2:$B$2000,"<>*Boo*",'020810'!$L$2:$L$2000,"=*Goat*")

 

[Big UT Fan]

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

 

[Eduardo]

Hi,
try

=COUNTIFS('020810'!$B$2:$B$2000,"<>*Bear*",'020810'!$L$2:$L$2000,"=*Goat*")

I changed Boo for Bear as per your last post.

 

[T. Valko]

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.