countif for multiple criteria?
 

Hi All,

Is there a way to count only if both conditions are met? for example, count
only if column E contains "*bat*" and column K contains "*yes*", otherwise
don't count it.

any suggestion?

thanks,
Tracktraining
--
Learning

countif for multiple criteria?
 

Any time you need multiple criteria, think SUMPRODUCT()

=SUMPRODUCT((E1:E100="*bat*")*(K1:K100="*yes*"))

see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
for the complete story.

--
Gary''s Student - gsnu200836


"tracktraining" wrote:

Hi All,

Is there a way to count only if both conditions are met? for example, count
only if column E contains "*bat*" and column K contains "*yes*", otherwise
don't count it.

any suggestion?

thanks,
Tracktraining
--
Learning

countif for multiple criteria?
 

Try

=COUNT(IF(ISNUMBER(SEARCH("BAT",E1:E13)),IF(ISNUMB ER(SEARCH("YES",K1:K13)),1)))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike



"tracktraining" wrote:

Hi All,

Is there a way to count only if both conditions are met? for example, count
only if column E contains "*bat*" and column K contains "*yes*", otherwise
don't count it.

any suggestion?

thanks,
Tracktraining
--
Learning

countif for multiple criteria?
 

Sumproduct and wildcards!! tested?

"Gary''s Student" wrote:

Any time you need multiple criteria, think SUMPRODUCT()

=SUMPRODUCT((E1:E100="*bat*")*(K1:K100="*yes*"))

see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
for the complete story.

--
Gary''s Student - gsnu200836


"tracktraining" wrote:

Hi All,

Is there a way to count only if both conditions are met? for example, count
only if column E contains "*bat*" and column K contains "*yes*", otherwise
don't count it.

any suggestion?

thanks,
Tracktraining
--
Learning

countif for multiple criteria?
 

Hi
try

=COUNTIF(e2:e15,"Bat")-COUNTIF(k2:k15,"yes")

"tracktraining" wrote:

Hi All,

Is there a way to count only if both conditions are met? for example, count
only if column E contains "*bat*" and column K contains "*yes*", otherwise
don't count it.

any suggestion?

thanks,
Tracktraining
--
Learning

countif for multiple criteria?
 

Tested with the asterisk a real asterisk, not a wildcard.
--
Gary''s Student - gsnu200836


"Mike H" wrote:

Sumproduct and wildcards!! tested?

"Gary''s Student" wrote:

Any time you need multiple criteria, think SUMPRODUCT()

=SUMPRODUCT((E1:E100="*bat*")*(K1:K100="*yes*"))

see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
for the complete story.

--
Gary''s Student - gsnu200836


"tracktraining" wrote:

Hi All,

Is there a way to count only if both conditions are met? for example, count
only if column E contains "*bat*" and column K contains "*yes*", otherwise
don't count it.

any suggestion?

thanks,
Tracktraining
--
Learning

countif for multiple criteria?
 

only if column E contains "*bat*" and column K contains "*yes*",

Apologies I read the post differently

Mike

"Gary''s Student" wrote:

Tested with the asterisk a real asterisk, not a wildcard.
--
Gary''s Student - gsnu200836


"Mike H" wrote:

Sumproduct and wildcards!! tested?

"Gary''s Student" wrote:

Any time you need multiple criteria, think SUMPRODUCT()

=SUMPRODUCT((E1:E100="*bat*")*(K1:K100="*yes*"))

see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
for the complete story.

--
Gary''s Student - gsnu200836


"tracktraining" wrote:

Hi All,

Is there a way to count only if both conditions are met? for example, count
only if column E contains "*bat*" and column K contains "*yes*", otherwise
don't count it.

any suggestion?

thanks,
Tracktraining
--
Learning

countif for multiple criteria?
 

When I need this condition I add a third column with a =Concatenate(e2,k2)
function and then =countif(range,batyes).. you can hide the third column.

"Eduardo" wrote:

Hi
try

=COUNTIF(e2:e15,"Bat")-COUNTIF(k2:k15,"yes")

"tracktraining" wrote:

Hi All,

Is there a way to count only if both conditions are met? for example, count
only if column E contains "*bat*" and column K contains "*yes*", otherwise
don't count it.

any suggestion?

thanks,
Tracktraining
--
Learning

countif for multiple criteria?
 

Thanks all for helping out. I tried every suggestion and the one that works
for me is Mikes:
=COUNT(IF(ISNUMBER(SEARCH("*xoft*",E1:E14)),IF(ISN UMBER(SEARCH("*yes*",K1:K14)),1)))


Thanks again.

--
Learning


"Mike H" wrote:

Try

=COUNT(IF(ISNUMBER(SEARCH("BAT",E1:E13)),IF(ISNUMB ER(SEARCH("YES",K1:K13)),1)))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike



"tracktraining" wrote:

Hi All,

Is there a way to count only if both conditions are met? for example, count
only if column E contains "*bat*" and column K contains "*yes*", otherwise
don't count it.

any suggestion?

thanks,
Tracktraining
--
Learning