Search code examples
google-sheetsarray-formulas

Sum of a range of cells that may contain + sign


I have a google sheet table with the cells A1, B1 and C1 containing either an integer, e.g., 12 or an expression <integer> + <integer>, e.g., 12 + 1 or 12+1.

I want to compute the sum of the cells in D1. Situation

|A1|  B1 | C1|
--------------
|1 | 1+1 | 3 |

should result in 6. Simply saying D1 = SUM(A1:C1) does not work: it ignores the cell B1.

I have found two partial solutions that I cannot combine:

  • SUM(SPLIT(B1;"+")) results in 2
  • if I had A2: 1, B2: 4 and C2: 9, then ArrayFormula(SUM(SQRT(A2:C2))) results in 6 (sum of square roots of these cells).

(e.g., ArrayFormula(SUM(SUM(SPLIT(A1:C1;"+")))) does not work properly)

How can I solve this problem? Two-step solution i) compute SUM(SPLIT(<cell>;"+")) in a new cell, and ii) compute the sum, is not an option.


Solution

  • Try this:

    =SUM(SPLIT(JOIN("+",A1:C1),"+"))

    In this solution you just need to join them first.

    BTW this formula works too:

    =SUM(SPLIT(JOIN("+",A1:C1),"+:)"))