0

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.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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