Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulacountififs

Combining multiple "COUNTIF" formula into one statement

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")
Solution

  • 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")