Counting a row based on criteria in 2 cols

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

Eduardo

Hi,

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

if this helps please click yes thanks
 
T

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)
 
B

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

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
 
E

Eduardo

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

Eduardo

Hi
use this

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

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
 
E

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top