Google-sheets – Add the numbers in rows where a string contains a given word

google sheets

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.

Best Answer

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

enter image description here

Related Topic