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.


3 Answers 3


Somehow this seems appropriate.


       IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""), 
     "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) ''"))


enter image description here


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.


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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy