Sum columns if text in another column matches

Question

I have a google spreadsheet. This sheet has a column of numbers and a column of text.

I would like to have the sum of the numbers in column where the corresponding text in the text column matches a string.

I have attempted to do this with the following formula but it doesn't work.

=SUM(IF(REGEXMATCH(M1:M,"Target-String"), I1:I ,0))

How do I rewrite this formula to achieve my aim ?

Any help will be appreciated

Answer 1

Just a clarification (since not enough data is provided)

SUMIF will ONLY give correct results if Target-String is the only text in cell.

=SUMIF(M3:M8,"Target-String",I3:I8)

REGEXMATCH will produce results for every occurrence of Target-String in a cell whether it is combined with other text or not.

=SUM(INDEX(IF(
   REGEXMATCH(M3:M33,"Target-String"), I3:I33 ,0)))

(Do adjust the formulae according to your ranges and locale)

Functions used:

• SUMIF
• SUM
• INDEX
• IF
• REGEXMATCH

Answer 2

I'm writing this answer as a community wiki since the solution was provided by @user11222393 in the comments section.

=sumif(M:M,"Target-String",I:I)

Reference:

• SUMIF