Counting a row based on criteria in 2 cols

  • Thread starter Thread starter Big UT Fan
  • Start date Start date
B

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.
 
Hi,

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

if this helps please click yes thanks
 
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)
 
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*"))
 
CONTAINS "Goat" AND col B DOES
NOT CONTAIN "Boo".
Click to expand...

What happened to "Bear" ? <g>

Looking at your formula:
=SUMPRODUCT(('020810'!$B$2:$B$2000<>"*Boo*")*('020810'!$L$2:$L$2000="*Goat*"))
Click to expand...

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
 
Hi,
why do you use wildcards is because Boo is contained in a text, could you
post what is in column B thanks
 
Hi
use this

=COUNTIFS('020810'!$B$2:$B$2000,"<>*Boo*",'020810'!$L$2:$L$2000,"=*Goat*")
 
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
 
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.
 
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.
 
Back
Top