Results 1 to 5 of 5

Thread: two values to find on the same cell

  1. #1

    two values to find on the same cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Can the below be shorten up? Its 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

  2. #2
    You could use

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

  3. #3
    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

  4. #4
    Quote Originally Posted by toony View Post
    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.
    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.

  5. #5
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •