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.

2 Answers 2


Short answer

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


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


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


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.