Count IF Column A CONTAINS value X AND Column B CONTAINS value y??

Hi All,

I am trying to write a formula that counts cells in column B that CONTAIN a value y IF the cells in column A CONTAIN a value X where X and Y are strings of letters. I capitalized CONTAINS because i have writen the following formula that works when the cells EQUAL X and Y but not when they contain:

=SUMPRODUCT(--(A1:A100="X"),--(B1:B100="Y"))


Any ideas on how to get this to work would be greatly appreciated!

Thanks
Keith

You can do it like this:

Formula:

=SUMPRODUCT(--(ISNUMBER(SEARCH("x",A1:A100),--(ISNUMBER(SEARCH("y",B1:B100))

SEARCH is not case sensitive, but if that is important to you then use FIND instead:

Formula:

=SUMPRODUCT(--(ISNUMBER(FIND("X",A1:A100),--(ISNUMBER(FIND("Y",B1:B100))

Obviously, "x" and "y" could be strings of letters.

Hope this helps.

Pete