How to get conditional formatting in Google Sheets if a cell contains specific formula/function?

Question

I would like to set conditional formatting in my GS for cells that not only are a formula, but have a specific type of formula.

I know I can do a conditional formatting to highlight cells that are formulas e.g. Custom Formula = =isformula(A1)

But what if I want the conditional formatting only to apply to certain kinds of formulas?

e.g. if the cell contains a formula like =A1+B1+C1, then leave it alone But if the cell contains a formula that uses the Sum function, e.g. =SUM(A1:A5), then highlight it.

I tried to do Custom Formula = =iferror(find("SUM",A1),false) for the range A1:D but it doesn't work the way I want it to.

Can this be done?

Answer 1

You can use the function FORMULATEXT to analyze the formula in a specific cell. Something like this should work:

=NOT(ISERR(FIND("SUM(",FORMULATEXT(E17))))

(here F17 and F18 show the formulas of E17 resp. E18)