Sum the numeric part of cells that contain specific strings

Question

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.

Answer 1

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.

Answer 2

Alternative:

=ArrayFormula(sum(iferror(value(1*substitute (A1:A,"pa ","")))))

SUM
IFERROR
VALUE
SUBSTITUTE

[In other words, I failed to spot this was yet another bump rather than a new post :) This formula is slightly shorter though.]