# countif for multiple criteria?

tracktraining
Guest
Posts: n/a

 4th Mar 2009
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

Gary''s Student
Guest
Posts: n/a

 4th Mar 2009
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

Mike H
Guest
Posts: n/a

 4th Mar 2009
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

Mike H
Guest
Posts: n/a

 4th Mar 2009
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

Eduardo
Guest
Posts: n/a

 4th Mar 2009
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

Gary''s Student
Guest
Posts: n/a

 4th Mar 2009
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

Mike H
Guest
Posts: n/a

 4th Mar 2009
> > > > 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

Alberto Ast
Guest
Posts: n/a

 4th Mar 2009
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

tracktraining
Guest
Posts: n/a

 4th Mar 2009
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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Tim3057 Microsoft Excel Programming 1 12th Jan 2006 06:17 PM sctroy Microsoft Excel Misc 2 25th Sep 2005 04:13 AM =?Utf-8?B?RWxsaWU=?= Microsoft Excel Worksheet Functions 4 15th Sep 2005 10:06 PM =?Utf-8?B?Sm9ubmllUA==?= Microsoft Excel Worksheet Functions 3 22nd Feb 2005 03:55 PM maswinney Microsoft Excel Worksheet Functions 3 16th Nov 2004 12:06 AM

Features