Determine if character appears in SUM function

[ChristineJ]

In cell A5, I'd like to return "Yes" if the text within any SUM function in E8 contains a "+" sign.

Should return "Yes" in A5 if E8: =SUM(D2+E2+F2)+SUM(K6:K19)+SUM(X4+Y4+Z4)
Should return "Yes" in A5 if E8: =SUM(D2:F2)+SUM(K6:K19)+SUM(X4+Y4+Z4)
Should return "Yes" in A5 if E8: =SUM(D2+E2+F2)
Should not return "Yes" in A5 if E8: =SUM(D2:F2)+SUM(K6:K19)+SUM(X4:Z4)
Should not return "Yes" in A5 if E8: =SUM(D2:F2)

The "+" signs between the SUM functions are not relevant to returning a "Yes".

Can this be done with either VBA or a formula? Thanks!

 

[JvdV]

Right, I didn't account for that scenario since we look for element starting with 'SUM(`, and not '=SUM('. So try:

=NOT(ISERROR(FILTERXML("<t><s>"&SUBSTITUTE(FORMULATEXT(A1),")+","</s><s>)")&"</s></t>","//s[starts-with(translate(.,'=*^-/',''), 'SUM(') and contains (.,'+')][1]")))

But note that the regex option provided by RoryA would still be more secure.

 

[ChristineJ]

Right, I didn't account for that scenario since we look for element starting with 'SUM(`, and not '=SUM('. So try:

=NOT(ISERROR(FILTERXML("<t><s>"&SUBSTITUTE(FORMULATEXT(A1),")+","</s><s>)")&"</s></t>","//s[starts-with(translate(.,'=*^-/',''), 'SUM(') and contains (.,'+')][1]")))

But note that the regex option provided by RoryA would still be more secure.

Many thanks for the follow up!

 

[Peter_SSs]

FWIW, two comments
- since SUM would always be in upper case in the formula I don't think the UDF needs the 'ignorecase' line
- since we only need to find a single case of a SUM with a "+" for the result to be TRUE/YES I don't think the UDF needs the 'Global' line

 

[Peter_SSs]

FWIW, two comments

- since SUM would always be in upper case in the formula I don't think the UDF needs the 'ignorecase' line
- since we only need to find a single case of a SUM with a "+" for the result to be TRUE/YES I don't think the UDF needs the 'Global' line

I also do not know what sort of formula might turn up but the UDF could fail (ie return FALSE) with a formula like

=SUM((D2)+(E2))

or perhaps a more 'legitimate' one like this (unless you do not want to flag this one?)

=SUM((E2-F2)/2,(X4+Y4+Z4)*2)

Do we need to try to think of ways to cope with that sort of thing or are you only expecting fairly standard/basic formulas?