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" <BigUTFan@discussions.microsoft.com> wrote in message 
news:BC655C02-B831-4C58-8C7A-DBCA659D65BF@microsoft.com...
>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*")*('020810'!$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*")*('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

-- 
Biff
Microsoft Excel MVP


"Big UT Fan" <BigUTFan@discussions.microsoft.com> wrote in message 
news:72FCE191-775A-4BB3-83BC-F405D326C0FE@microsoft.com...
> 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*"))
>
> "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*")*('020810'!$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*")*('020810'!$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" <BigUTFan@discussions.microsoft.com> wrote in message 
> news:BC655C02-B831-4C58-8C7A-DBCA659D65BF@microsoft.com...
> >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" <BigUTFan@discussions.microsoft.com> wrote in message 
> > news:BC655C02-B831-4C58-8C7A-DBCA659D65BF@microsoft.com...
> > >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" <BigUTFan@discussions.microsoft.com> wrote in message 
news:8EA11DC5-D5C6-4F53-AAEA-4978DF91E55E@microsoft.com...
> 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" <BigUTFan@discussions.microsoft.com> wrote in message
>> news:BC655C02-B831-4C58-8C7A-DBCA659D65BF@microsoft.com...
>> >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.
>>
>>
>> .
>>