two values to find on the same cell

T

toony

New member
Joined
Mar 6, 2014
Messages
49
Reaction score
0
Points
0
Location
UK
Excel Version(s)
Excel 2016
Can the below be shorten up? It’s basically to count if “dr” or “ba” are in cell E2180;


=IF(ISNUMBER(SEARCH("(dr)",E2180)),"1",IF(ISNUMBER(SEARCH("(ba)",E2180)),"1",""))

Many thanks
 
You could use

=SUM(COUNTIF(E21,{"*(dr)*","*(ba)*"}))
 
Hi Bob,

Thank your for your help. Your formula worked, but it only counted it as one if they appeared, when I needed for it to give BA or DR, depending which one appeared on it. In the end somebody came up with formula =LOOKUP("ZZ",REPT({"DR","BA"},SEARCH({"DR","BA"},A1)^0)). It is very cool but don't quite understand "ZZ" and ^0. Don't know if you can help me to understand this.

Thanks Bob
 
toony said:
Your formula worked, but it only counted it as one if they appeared, when I needed for it to give BA or DR, depending which one appeared on it.
Click to expand...

Where in your original post does it say that? You gave an example that returned 1 and asked for it to be shortened. That I did. This other formula finds the first of the pair found. Completely different objectives.
 
Sorry there if not properly explained ver y well.....actually, i did use the one u gave me in another case, so it was useful anyway
 
You must log in or register to reply here.
Back
Top