Need help creating formula array to look for text in cells

[johnelstad]

I have two columns. The first has a string of text, (e.g., "010812_Sports&Outdoors_Exercise&Fitness_L2_Story1G1"). The second will either have "CMS" or "OC" in it. Thus far, I've been able to create a few formula arrays to show me the sum of all rows where there is both (for example) "sports" in column 1 and "cms" in column 2. Here's a sample formula:


{=SUM((ISNUMBER(SEARCH("sports",$B$2:$B$500)))*(ISNUMBER(SEARCH("cms",$E$2:$E$500))))}


However, now I need to do more advanced text searching: finding any instance of several strings, but not others. The English version of the query would be, "Add up all the instances where a cell in column 1 has either "electronics" OR "toys", but NOT "video", and the same cell in the second column has "cms."


I've spent a few hours on this using SEARCH, COUNTIF, and others, but to no avail. Can you help?


Thanks!

 

[Luke M]

I think this will do it.


=SUMPRODUCT((ISNUMBER(SEARCH("electronics",$B$2:$B$500)))+(ISNUMBER(SEARCH("toys",$B$2:$B$500)))*(ISERROR(SEARCH("video",$B$2:$B$500)))*(ISNUMBER(SEARCH("cms",$E$2:$E$500))))

 

[johnelstad]

That did the trick! Thanks, Luke, for the fast help!


Now I understand why my formulas weren't working; I was trying to do too much within each function instead of just breaking them up. I also hadn't considered excepting "video" using the ISERROR command. I'll be using that a lot.


Thanks again!