MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Counting a row based on criteria in 2 cols

• Follow

```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
```
 0

```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.
```
 0

```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.

```
 0

```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.
```
 0

```>CONTAINS "Goat" AND col B DOES
>NOT CONTAIN "Boo".

What happened to "Bear" ? <g>

>=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.

```
 0

```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.
```
 0

```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.
```
 0

```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"))

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.
>
>
> .
>
```
 0

```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"))
>
> 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.
> >
> >
> > .
> >
```
 0

```>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"))
>
> 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.
>>
>>
>> .
>>

```
 0

9 Replies
134 Views

8/19/2012 2:40:07 AM