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

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

Try

=COUNT(IF(ISNUMBER(SEARCH("BAT",E1:E13)),IF(ISNUMBER(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

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

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

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

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

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

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


Thanks again.

--
Learning


"Mike H" wrote:

> Try
>
> =COUNT(IF(ISNUMBER(SEARCH("BAT",E1:E13)),IF(ISNUMBER(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