0

Situation:

I want to search for a range of specific column in order to check whether it contains multiple specific strings or not. If selected strings found, it will returns a string based on the following conditions.

contain "#ERROR!" -> return "Partly Abnormal"

contain "#N/A" -> return "Abnormal"

contain "#N/A" and "#ERROR!" -> return "Abnormal"

No above strings -> return "Normal"

Problem:

For this purpose, I typed the following formula but I tried several hours and still have no idea how to combine both into one. Your help is high appreciated, thank you!

=IF(COUNTIF(A2:A,"#N/A"),"Abnormal","Normal")

=IF(COUNTIF(A2:A,"#ERROR!"),"Partly Abnormal","Normal")
player0
  • 124,011
  • 12
  • 67
  • 124
Dennis
  • 67
  • 1
  • 9

2 Answers2

0

Use IFS instead of IF

This allows you to combine several conditions in one.

Mind: IFS does not allow to specify a default condition if non of the specified strings are found, but you can do it with a atrick by specifying as the last condition true:

=IFS(COUNTIF(A2:A,"#N/A"),"Abnormal", COUNTIF(A2:A,"#ERROR!"),"Partly Abnormal", true, "Normal")
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
0

nested IF

=IF(COUNTIF(A2:A,"#N/A"), "Abnormal",
 IF(COUNTIF(A2:A,"#ERROR!"), "Partly Abnormal", "Normal"))

0

player0
  • 124,011
  • 12
  • 67
  • 124