countifS notice the 'S'

[eddiegnz1]

in Excel 2010, how can i use countifS to count the number of times column 'B' has initials "EG" AND column 'D' has the word "yes". The problem is that column D has the word "yes" along with other characters such as "TR/yes".

my formula is currently =COUNTIFS(B10:B3000,I1,D10:D3000,"yes")

column B has many initials and column D has any number of different characters with sometimes the word yes in column D.

not working

kindest thanks,
Eddie

 

[Joe4]

Try:
=COUNTIFS(B10:B3000,I1,D10:D3000,"*yes")

 

[Domenic]

If "yes' occurs at the end of the text string, try..

=COUNTIFS(B10:B3000,I1,D10:D3000,"*yes")

or

=COUNTIFS(B10:B3000,I1,D10:D3000,"*"&G2)

If "yes" can occur anywhere within the text string, try...

=COUNTIFS(B10:B3000,I1,D10:D3000,"*yes*")

or

=COUNTIFS(B10:B3000,I1,D10:D3000,"*"&G2&"*")

...where G2 contains "yes".

Hope this helps!

 

[eddiegnz1]

awesome, thank you so much. the asterisks did it.

 

[Domenic]

You're very welcome!