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.

Hi,

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

if this helps please click yes thanks

"Big UT Fan" wrote:

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.

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.

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*")*('020 810'!$L$2:$L$2000="*Goat*"))

"Eduardo" wrote:

Hi,

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

if this helps please click yes thanks

"Big UT Fan" wrote:

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.

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*")*('02 0810'!$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

--
Biff
Microsoft Excel MVP


"Big UT Fan" wrote in message
...
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*")*('020 810'!$L$2:$L$2000="*Goat*"))

"Eduardo" wrote:

Hi,

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

if this helps please click yes thanks

"Big UT Fan" wrote:

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.

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

"Big UT Fan" wrote:

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*")*('020 810'!$L$2:$L$2000="*Goat*"))

"Eduardo" wrote:

Hi,

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

if this helps please click yes thanks

"Big UT Fan" wrote:

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.

Hi
use this

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

"Big UT Fan" wrote:

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*")*('020 810'!$L$2:$L$2000="*Goat*"))

"Eduardo" wrote:

Hi,

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

if this helps please click yes thanks

"Big UT Fan" wrote:

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.

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.


.

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.


"Big UT Fan" wrote:

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.


.

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.


.