Google-sheets – Google Sheets formula IF or SUM with conditional formatting

The bounded script includes two onEdit functions this is not a good practice by one hand because this could lead to confusions and by the other only one will be executed usually the one on the last gs file.

Another observation, in this case regarding the code in the question, clearContent syntax doesn't include an argument. In other words, instead of

s.getRange("D4:D18").clearContent(null)

the right syntax is

s.getRange("D4:D18").clearContent();

and instead of

s.getRange("D4:D18").clearContent("D4:D18");

the right syntax is

s.getRange("D4:D18").clearContent();

NOTE: When changing Template!D3 to 2hr Delay the execution transcript shows the following error

Execution failed: Cannot find method clearContent(null). (line 8, file "WIP")

Solved my own question, in a bit of a brute force method. Since Google sheets was a bit iffy about letting me do an AND statement on ISNUMBER(SEARCH()), I added 3 helper columns which I hid:

• Column P =ISNUMBER(SEARCH("Cancelled",$O2)) *Checking for Cancelled on in a string TRUE/FALSE
• Column Q =IF($P2=TRUE,1,0) *Returns 1 in any column with TRUE for Cancelled
• Column R =$Q2+$B2 *Adds a 1 to any order number where the previous formula returns a 1

I then used =countif($R:$R,R2)>1 and applied it to column B.