Skip to main content

I need some help with the IF formula

  • March 14, 2020
  • 4 replies
  • 61 views
M

I have a tab with a list of hospitals. Some of them have the tag “COE”.
On an other tab, I’m listing the feedback gathered from hospitals.

Some feedback came from many different hospitals.
So I add the name of the hospitals on the column “requesters”

Now, I want to sort my list of feedback, and put on the top the feedback coming from “COE” hospitals.

How can I do that?

I thought to use a formula to have “COE” on the name of my hospital, and do something like “IF on the requester list there is an hospital with “COE” on it, then check the box” (for example).
But I have NO IDEA how do do this… and there is maybe an even easier way to do it…

Thank you very much for your help!

4 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • March 14, 2020

What about something like this?

IF(SEARCH("COE", {requesters}), "COE")
    M
    • Maud_Benaddi
    • Author
    • New Participant
    • March 17, 2020

    What about something like this?

    IF(SEARCH("COE", {requesters}), "COE")

    It works! Thank you very much!!!
    And just 2 more questions (because I really have a lot of trouble with the formula:

    1. How does it looks like if I have 2 different columns of requesters?
    2. How does it looks like, if on the top of that, instead of writing “COE” on my column, I want to write the number of "COE"s detected on my 2 requesters columns?

    Thank you VERY much!

    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • March 18, 2020

    It works! Thank you very much!!!
    And just 2 more questions (because I really have a lot of trouble with the formula:

    1. How does it looks like if I have 2 different columns of requesters?
    2. How does it looks like, if on the top of that, instead of writing “COE” on my column, I want to write the number of "COE"s detected on my 2 requesters columns?

    Thank you VERY much!


    Well, those formulas would be much more complicated.

    This formula will tell you the number of "COE"s in a field.

    (
  LEN({requesters}) 
  -
  LEN(
    SUBSTITUTE(
      {requesters},
      "COE",
      ""
    )
  )
)
/ 3

    If you have multiple fields, you can have an even more complicated formula. Here I have collapsed the above formula to save space. You would need to edit the name of requesters2 to match your actual field value.

    ((LEN({requesters}) - LEN(SUBSTITUTE({requesters},"COE","")))/3)
+
((LEN({requesters2}) - LEN(SUBSTITUTE({requesters2},"COE","")))/3)
      M
      • Maud_Benaddi
      • Author
      • New Participant
      • March 18, 2020

      Well, those formulas would be much more complicated.

      This formula will tell you the number of "COE"s in a field.

      (
  LEN({requesters}) 
  -
  LEN(
    SUBSTITUTE(
      {requesters},
      "COE",
      ""
    )
  )
)
/ 3

      If you have multiple fields, you can have an even more complicated formula. Here I have collapsed the above formula to save space. You would need to edit the name of requesters2 to match your actual field value.

      ((LEN({requesters}) - LEN(SUBSTITUTE({requesters},"COE","")))/3)
+
((LEN({requesters2}) - LEN(SUBSTITUTE({requesters2},"COE","")))/3)

      It works!! You’re the best, thank you very much!! :tada:

        Terms & ConditionsAccessibility statement