Add the numbers in rows where a string contains a given word

Question

I have two columns a and b

• a contains a set of text and
• b contains a set of numerical that relates to each row in a

I want to create a function that will search column a for substring value and then return a total value by adding together the corresponding value from column b where the string was found

E.g. I want to find devops in column a

devops  90500
devopsreactions 8100
devops wiki 1000
devopsdays  720
devops borat    390
devops tumblr   320

and for the formula to return a sum of corresponding values in column b for every time the string is found in column a.

Answer 1

Somehow this seems appropriate.

Formula

=ARRAYFORMULA(
   QUERY(
     {
       IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""), 
       B2:B
     }, 
     "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''"
   )
 )

copy / paste
=ARRAYFORMULA(QUERY({IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""), B2:B}, "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''"))

Screenshot

Answer 2

I would filter using regexmatch and then sum:

=sum(filter(B2:B, regexmatch(A2:A, "devops")))

if you want to include "devopsreactions", or

=sum(filter(B2:B, regexmatch(A2:A, "\bdevops\b")))

if you are looking for "devops" as a separate word.

Answer 3

Shorter query version:

=index(query(A:B,"select sum(B) where A contains 'devops'"),2,1)

Includes where string is found as part of a longer string. (Change contains to = for only where the entire cell content or to like and devops to devops% for matches at start of words only.)