[Solved] Check for specific text within text in a cell

[cid]

Right now I have a formula that adds together a range of cells corresponding to another column of cells, but only the cells that contain specific text ("TEXT" in the example):

Code: Select all

=SUMPRODUCT(G8:G58="TEXT"; J8:J58)

However, sometimes the text might be entered in the G-Range as "TEXT abc", where "abc" is any possible text afterwards. It can be put into any box in the column with anything written after "TEXT". All I can control is that the text that's being searched for is spelled the same way every time and always comes in front of the variable text.

So how do I get my code to trigger no matter what else is written in the cell besides "TEXT"? Is there a way to search for if the string "TEXT" is present in any position and non-case sensitive?

Also, is the "SUMIF" command better in this case or is it essentially the same?

Also if you're wondering I do have other code checking for if it's anything other than "TEXT" but it's being used in a different formula.

Thanks!

[FJCC]

Try

Code: Select all

=SUMPRODUCT(ISNUMBER(SEARCH("^TEXT";G8:G58));J8:J58)

The ^ at the start of the search string assures that TEXT is at the beginning of the text. For this to work, you have to go to the menu Tools -> Options, expand the Calc list on the left, select Calculate and turn non Enable Regular Expressions in Formulas.

[cid]

This seemed to work, thanks!