Search for text strings with multiple conditions

M

minismood

Hello!

I want to search for the word "Hello" in columns which can consist of
"Hello;Hi;Greetings" and want the hello must based on several
conditions. Now I use this formula, but then I can´t find the cells
which contains more text than just "Hello":

=SUM(IF(BP33:BP1000=BP8;IF(BR33:BR$1000="Hello";1;0)))

I want the formula to be like this:

=SUM(IF(BP33:BP1000=BP8;IF(BR33:BR$1000="*Hello*";1;0)))

But the "*Hello*" doesn´t work...does anyone know a formula which is
very similar to the one above but can search through multipe texts for
a text string??

I hope you know what I mean... :)
 
K

kounoike

i'm not quite sure, but does this work in your case?

=SUMPRODUCT((BP33:BP1000=BP8)*NOT(
ISERROR(SEARCH("*Hello*",BR33:BR$1000))))

keizi

Hello!

I want to search for the word "Hello" in columns which can consist of
"Hello;Hi;Greetings" and want the hello must based on several
conditions. Now I use this formula, but then I can´t find the cells
which contains more text than just "Hello":

=SUM(IF(BP33:BP1000=BP8;IF(BR33:BR$1000="Hello";1;0)))

I want the formula to be like this:

=SUM(IF(BP33:BP1000=BP8;IF(BR33:BR$1000="*Hello*";1;0)))

But the "*Hello*" doesn´t work...does anyone know a formula which is
very similar to the one above but can search through multipe texts for
a text string??

I hope you know what I mean... :)
 
Top