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!

 

[RoryA]

Will the cell only ever have a series of SUM()+SUM() functions in it, or are there other possibilities?
Also, do you have functions like TEXTSPLIT?

 

[RobP]

I can understand @Rorys questions.

This might get you what you want (as a formula for E8), but its by no means bomb proof .. if for example you are setting an exam test or the like.. what I mean is, it could be fooled by a mistake on the user part. So I post it only on the basis you understand that its bit of a hack. But if it helps you, then great.. otherwise Rory is gonna come up with something more bomb proof once you answer his questions

=IF(ISNUMBER(SEARCH("2+",FORMULATEXT(E8))+(SEARCH("4+",FORMULATEXT(E8)))),"YES","NO")

 

[ChristineJ]

It is possible there could be other functions, such as IF, in the formula string. SUM is the only one I'm interested in knowing if "+" signs are used.

I don't have TEXTSPLIT, TEXTAFTER, or TEXTBEFORE. I might have been able to work it out if I had those... I have Excel for Microsoft 365 - should be the latest version since I just bought it.

Thanks so much!

 

[RoryA]

I think this UDF would work:

Function HasSumWithPlus(checkCell As Range)
   Dim formulaText As String
   formulaText = checkCell.Formula
   
   With CreateObject("vbscript.regexp")
      .Pattern = "SUM([^)]*\+[^)]*)"
      .ignorecase = True
      .Global = True
      HasSumWithPlus = .Test(formulaText)
   End With
End Function

 

[ChristineJ]

I can understand @Rorys questions.

This might get you what you want (as a formula for E8), but its by no means bomb proof .. if for example you are setting an exam test or the like.. what I mean is, it could be fooled by a mistake on the user part. So I post it only on the basis you understand that its bit of a hack. But if it helps you, then great.. otherwise Rory is gonna come up with something more bomb proof once you answer his questions

=IF(ISNUMBER(SEARCH("2+",FORMULATEXT(E8))+(SEARCH("4+",FORMULATEXT(E8)))),"YES","NO")

Thanks for the suggestion. I tested it and it seems it is also considering the plus signs between the SUM functions. I only want it to look to see if there are plus signs within the parenthesis of the SUM functions.

 

[ChristineJ]

I think this UDF would work:

Function HasSumWithPlus(checkCell As Range)
   Dim formulaText As String
   formulaText = checkCell.Formula
  
   With CreateObject("vbscript.regexp")
      .Pattern = "SUM([^)]*\+[^)]*)"
      .ignorecase = True
      .Global = True
      HasSumWithPlus = .Test(formulaText)
   End With
End Function

YES! Just tested your UDF - does exactly what I need! Thank you so much for all the work in writing this customized code for me! All the best, C

 

[JvdV]

Have a go with:

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

The idea here:

* Split FORMULATEXT() using FILTERXML() by putting xml end/start tags before each occurence of '+SUM(';
* Check each node against: '[contains(., '+') and starts-with(., 'SUM(')]' to see that there is at least an element that starts with 'SUM(' and does contain a '+';
* '[1]' would then return only a single element from whatever elements remain.

 

[ChristineJ]

Have a go with:

=NOT(ISERROR(FILTERXML("<t><s>"&SUBSTITUTE(FORMULATEXT(A1),"+SUM","</s><s>")&"</s></t>","//s[contains(., '+')][1]")))

The idea here is that we split the formulatext using filterxml and use xpath expressions to test against the remaining elements.

Awesome - yes, this does work perfectly. Appreciate your explanation as well. Thanks so much!

 

[ChristineJ]

Awesome - yes, this does work perfectly. Appreciate your explanation as well. Thanks so much!

Actually, just noted that =SUM(D11+E11)+F11*J$19 is returning false, although there is a plus sign in the SUM function.