countif for multiple criteria?

  • Thread starter Thread starter tracktraining
  • Start date Start date
T

tracktraining

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
 
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
 
only if column E contains "*bat*" and column K contains "*yes*",

Apologies I read the post differently

Mike
 
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.
 
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.
 
Back
Top