Google-sheets – Sum the numeric part of cells that contain specific strings

google sheets

I am struggling to find a way to sum the numeric part of cells that contain specific strings. Let's say that: cell A1: pa 23.45, cell A2: ts 12.35,
cell A3: pa 10.55.

The result of the sum of A1:A3 I would like to be 34 (23.45+10.55), which is the sum of only A1 and A3 because they contain pa.

So, find the cells in a range that contains pa and sum the numeric part of them.

I don't want to have separate cells for string and numeric part.

Best Answer

Short answer

=ArrayFormula(sum(value(IFERROR(REGEXEXTRACT(A1:A3,"pa ([^/d]*)"),))))

Explanation

  • REGEXEXTRACT(A1:A3,"pa ([^/d]*)") returns the text at the right of pa that starts with any digit, if a matching string is not found, then returns an error.
  • IFERROR replaces the error result by a blank.
  • value convert the strings to numbers if the string is a valid number.
  • sum sums the values.
  • ArrayFormula makes the result of the above calculation to be expanded to the required area.
Related Topic